cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filter a Measure by the LastDate for a Category

Hi, I have a simple Matrix Below. The rows are 'Calendar'[Month] & 'Commercial Reviews'[SubmissionType].

 

I have a measure to return a Budget = CALCULATE(AVERAGE('Commercial Reviews'[WOL Margin %]),'Commercial Reviews'[SubmissionType]="Budget").

 

Where the Budget is blank I would like another Measure 'Budget Lookup' to return the last Budget value by Date. So for Instance April and MAY should return 25% but September should remain at 19.8%.

 

I've made start with the DAX measure and i've tried a few things to filter on the lastdate but can't get it to work.

 

Budget Lookup =
CALCULATE(AVERAGE('Commercial Reviews'[WOL Margin %]),ALL('Calendar'[Month]),'Commercial Reviews'[SubmissionType]="Budget")
 
SharedScreenshot.jpg

 

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Filter a Measure by the LastDate for a Category

@dmbd1904 ,

Modify your DAX like below:

Budget Lookup =
CALCULATE(AVERAGE('Commercial Reviews'[WOL Margin %]),FILTER(ALL('Calendar'[Month]),'Commercial Reviews'[SubmissionType]="Budget"))
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report
Highlighted
Helper I
Helper I

eRe: Filter a Measure by the LastDate for a Category

Hi @Tahreem24 , thanks for the respoinse. Unfortunately that returns the following error....

 

A single value for column 'SubmissionType' in table 'Commercial Reviews' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Also, would the measure need to use LastDate or similar in order to chose the latest budget values?

Highlighted
Super User IX
Super User IX

Re: Filter a Measure by the LastDate for a Category

@dmbd1904 , You can try lastnonblankvalue . refer

https://community.powerbi.com/t5/Quick-Measures-Gallery/Finanical-Magic-to-continue-with-10-Recently...

 

If they are on the last date of the month - you can use closingbalancemonth 

 

https://www.facebook.com/watch/?v=343509629992272



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors