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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.