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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.