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

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 II
Super User II

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 II
Helper II

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 IV
Super User IV

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors