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
Anonymous
Not applicable

Combine two measures

Hello, 

 

I have two measures that I would like to combine into one.    The reason for this is the one of the measures gives me part of the result I am looking for, and the other measure gives me the other part.  

 

Measure 7 gives me the full month values for Jan and Feb because the formula looks at the 1st day of the next month  (our accounting system reports reconciled numbers the next day....so Feb 1 it reports the full month of Jan....Mar 1 it reports the full month of Feb...and so on.  

 

But because March is not finished yet, the value in the 4th column is blank (see table below).    I do get the correct MTD value for March using the lastnonblankvalue measure below  (see March in 5th column).  

 

Somehow, I would like to combine the two of these?  

 

Last Nonblank = LASTNONBLANKVALUE ( Dates[Date], [Actual Value Measure] )*-1

 

 

Measure 7 =
VAR _year = SELECTEDVALUE(Dates[Year])
VAR _month = SELECTEDVALUE(Dates[MonthName])
VAR _date = CALCULATE(MIN(Dates[Date]), FILTER(ALL(Dates), Dates[Year] = _year && Dates[MonthName] = _month))
VAR _lastDate = EOMONTH(_date, -0) +1

RETURN
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = _lastDate))*-1
 
last non.png
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Greg_Deckler   Genius Greg!   And so elegant.   

 

Will mark as solved...but one additional question if I may:    The grand total is not summing correctly on any of my previous tries and also on the one you just gave me.   Is there a way to fix that?      I'd ideally like it to sum the three months.  

 

Greg1.png

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Measure = IF(ISBLANK([Measure 7]),[Last Nonblank],[Measure 7])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler   Genius Greg!   And so elegant.   

 

Will mark as solved...but one additional question if I may:    The grand total is not summing correctly on any of my previous tries and also on the one you just gave me.   Is there a way to fix that?      I'd ideally like it to sum the three months.  

 

Greg1.png

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.