What this is going to explain below is how I use a parameter that a user has entered to make a Dynamic Calculated Measure based on the Parameter entered.

Example Scenario

  • In my model I have got Parameters for the Fiscal Start Year
  • So I want to create a calculated Measure that will get me the values for 4th Year going forward
    • So what we are looking to calculate is the following, which is the Value for 2015-16
  • And then if we change the Parameters for the Fiscal Start Year as shown below
  • So I want to create a Calculated Measure that will get me the values for 4th Year going forward
    • So what we are looking to calculate is the following, which is the Value for 2017-18
  • But using the same calculation

Getting the Parameter into a table

In the steps below I am going to explain how to get our Parameter Value for the Fiscal Start Month into a table in the Query Editor

  • Go into the Query Editor
  • Then click on New Source, and select Blank Query
  • Now in my Example I name the Query “Parameter – Fiscal Start Year”
  • Next I clicked on Advanced Editor
  • I then put in the following M Code

    let

    Source = #table({“Fiscal Start Year Number”},{{#”Fiscal Start Year”}})

    in

    Source

    • As you can see above I created a table with the Column Name “Fiscal Start Year Number” highlighted in PURPLE
    • And then defined the column to have the Parameter called #”Fiscal Start Year”
      highlighted in LIGHT BLUE
  • This then results in the following table as shown below.
  • What I then did was to use the standard Query Editor Functions and changed it to the Data Type of Whole Number
  • NOTE: This is so that I can then use it to go backwards and forwards for my dynamic calculated measure.
  • The final step was to load it into my Power BI Desktop Model

Creating the Dynamic Calculated Measure

Next I will explain how I created my dynamic calculated measure, which will change based on what the Fiscal Start Date Parameter that has been selected.

  • Below is my Dynamic Calculated Measure syntax with the explanation what I did afterwards.

    Total Value for 3rd Year =

    CALCULATE (

    [Total Value],

    FILTER (

    ‘Budget Data without New Year’,

    ‘Budget Data without New Year'[Fiscal Year Number]

    = VALUES ( ‘Parameter – Fiscal Start Year'[Fiscal Start Year Number] ) + 3

    )

    )

    • What I have done is to name and start my Calculated Measure and put in the filter.
    • The part where the magic happens in the Filter
      • In here is where I specify that I want to look at the ‘Budget Data without New Year'[Fiscal Year Number] and compare that with the VALUES from my parameter ‘Parameter – Fiscal Start Year'[Fiscal Start Year Number]
      • And then I always want to go forward by 3 to get the 4th year going forward
  • So once this has run I should get the following as shown previously for Fiscal Start Year Parameter 2012
    • And I get the following below.
  • So if I change the Fiscal Start Year Parameter to 2014 once this has run I should get the following as shown previously.
    • And I get the following below.

Conclusion

By using Parameters and putting it into a table means that this can then be used to dynamically create other calculated measures.

You can download the PBIX file with the example data here: UnPivot Other Columns.pbix