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
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
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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors