Max date period as part of calculation

[Anonymous]

Hi all

New to PowerBI and have come over from Qlik usage. I am trying to create a measure that gives me the latest value for a sales measure where in Qlik I would use the MAX(Period) as a way to force the KPI I create to show only that value.

I tried to do the same with PowerBI after reading some forum posts here but am getting stuck with an error message. 

My expression is as follows:

Current Sales Growth = CALCULATE(average('Prod Data'[Sales]),'Prod Data'[Period]=MAX('Prod Data'[Period]))

I have verified using a card object that doing MAX('Prod Data'[Period]) gives me the right value in the data and that when I manually entered a date in the above (eg 'Prod Data'[Period]=VALUE("30/09/2017")) that this also worked.

However I'd like the calc to be dynamic instead of static for the dataset.

Any help is appreciated thank you!

[DAX0110 · Accepted solution]

Hi @Anonymous, try this version:

Current Sales Growth =

    VAR lastPeriod = MAX('Prod Data'[Period])

    RETURN CALCULATE(average('Prod Data'[Sales]),'Prod Data'[Period]=lastPeriod)

[Anonymous]

Did not know variables could be used that way, that is brilliant!

I wonder why I could not just put it all inthe one script though? Is that nuance particular to PowerBI?

thanks for your quick help!

[DAX0110]

Hi @Anonymous, if you're interested in delving deeper into this intriguing phenomenon, I'd suggest this page:

https://powerpivotpro.com/2012/06/filter-when-why-how-to-use-it/