cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Max date period as part of calculation

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!

1 ACCEPTED SOLUTION
DAX0110
Resolver V
Resolver V

Hi @Anonymous, try this version:

 

Current Sales Growth =

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

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

 

 

View solution in original post

3 REPLIES 3
DAX0110
Resolver V
Resolver V

Hi @Anonymous, try this version:

 

Current Sales Growth =

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

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

 

 

View solution in original post

Anonymous
Not applicable

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!

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/

 

 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.