Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
dmbd1904
Helper III
Helper III

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

@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

Tahreem24
Super User
Super User

@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!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.