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
scaast
Frequent Visitor

MTD Budget from a table that includes an equally distributed monthly budget??

I have a monthly budget table that has the budget distributed equally across all days of the month.  When I do a TOTALMTD, it results in the entire month's budget amount.  I only want the result to be the MTD budget total.

 

For example:  If I have a $100k budget for March, it would be distributed evenly at $3,225.81 per day.   I expect my TOTALMTD on the 7th of March to be $22,580.67.   The result I'm getting with the formula below is $100k.  What am I doing wrong?

 

MTD Budget = TOTALMTD(sum(dimBudget[Budget]),dimDate[Date])

 

Thank you for your help.

1 ACCEPTED SOLUTION

@Phil_Seamark , thank you for your help.

 

I tweaked your solution slightly to get the number I needed.  

 

MTD Budget = CALCULATE (SUM(dimBudget[Budget]),
FILTER (
ALL ( dimDate ),
'dimDate'[Month Number] = MAX ( 'dimDate'[Month Number] )
&& 'dimDate'[Date] <= MAX ( dimVendorMetrics[Date] )))

View solution in original post

7 REPLIES 7
mattbrice
Solution Sage
Solution Sage

TOTALMTD finds the last date visible in the current filter context and then expands the filter to include all the dates for the month to that last date.  TOTALMTD is really just syntax sugar for the code shown by Phil_Seamark.  So if you are slicing or selecting the entire month, then that is what gets passed to the filter context.  You need to slice or select the max date you want the measure to compute through, then TOTALMTD will work.  

Phil_Seamark
Employee
Employee

Hi @scaast

 

I think this pattern is possibly what you are looking for

 

MTD Budget = CALCUATE (SUM(dimBudget[Budget]),
FILTER ( ALL ( dimDate ), 'dimDate'[YearMonthNumber] = MAX ( 'dimDate'[YearMonthNumber] ) && 'dimDate'[Date] <= MAX ( 'dimDate'[Date] ) )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks @Phil_Seamark

 

My dimDate table has the following columns available:

 

Date

Day of Week

Month

Month Number

Quarter

Week Number

Year

 

I edited your solution to this:

 

MTD Budget = CALCULATE (SUM(dimBudget[Budget]),
FILTER (
ALL ( dimDate ),
'dimDate'[Month Number] = MAX ( 'dimDate'[Month Number] )
&& 'dimDate'[Date] <= MAX ( 'dimDate'[Date] )))

 

This still resulted in the entire month budget amount being provided, rather than MTD.

Do you have a 1 to many relationship between dimBudget and dimDate?

 

Are both columns Date?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

I've built a small PBIX file with it working here which you can download to have a play

 

MTD Example

 

Both formulas should be working ok.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark , thank you for your help.

 

I tweaked your solution slightly to get the number I needed.  

 

MTD Budget = CALCULATE (SUM(dimBudget[Budget]),
FILTER (
ALL ( dimDate ),
'dimDate'[Month Number] = MAX ( 'dimDate'[Month Number] )
&& 'dimDate'[Date] <= MAX ( dimVendorMetrics[Date] )))

Oh so you got it working ok?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.