I was working on a Power BI Model where I had a requirement where I wanted to use the Last value from an [Actuals] calculated measure as part of another calculated measure [Pro-rata Budget Selection].

Now due to the way the filter context works, when I tried the following calculated measure below, it would return the value where the Fiscal Quarter was Q4, when what I was wanting to get back was Q2. And this was due to the filter context being applied differently across the different calculated measures.

CALCULATE (
[Pro-rata Budget Selection],
FILTER (
'Date',
'Date'[Fiscal Quarter]
  = LASTNONBLANK('Date'[Fiscal Quarter],[Actuals])

So in order to solve this issue I created a calculated table which would only have the value of Q2.

Solution

I created the table below so that subsequently every time the data is updated, the Fiscal Quarter would change based on the data.

So below is the syntax with the explanation afterwards.

  • First I clicked in the Modeling Ribbon and selected New Table
  • Next I put in the syntax
    Calculated Table - LastNonBlank =
CALCULATETABLE (
LASTNONBLANK ( 'Date'[Fiscal Quarter], [Actuals] ),
 'Date'[Current Year] = VALUES ( 'Current Year'[Current Year]
 )
)
  • The important piece of DAX syntax is highlighted in LIGHT BLUE above. Starting with the LASTNONBLANK
    • As you can see I got the last non blank value for the Fiscal Quarter from my Date table based on the [Actuals] calculated measure.
  • Then I added in an additional filter in which I filtered for the ‘Date'[Current Year]
    highlighted in ORANGE
    • NOTE: The ‘Current Year'[Current Year] is simply taken my Parameter called “Current Year” and converted it to a list.
      • When you do this, it imports it into the Power BI Model, which makes it very useful for when you want to use it as part of other calculated measures.
  • So the output of this table is shown below.
  • As you can see above I now have a Text Value of Q2

So my original calculated measure now changed to the following below.

CALCULATE (
[Pro-rata Budget Selection],
FILTER (
'Date',
'Date'[Fiscal Quarter]
    = VALUES ( 'Calculated Table - LastNonBlank'[Fiscal Quarter] )

Conclusion

So this enabled me to have a value, which I could use in any calculation due to the fact that in the Power BI Model it is seen as a table value.

So that meant I could use it very easily as part of any calculated measure, as shown above.