Forum Discussion

scaast's avatar
scaast
Frequent Visitor
9 years ago
Solved

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 b...
Need Help
  • scaast's avatar
    scaast
    9 years ago

    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] )))

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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] )
    )
scaast's avatar
scaast
Frequent Visitor
9 years ago

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.

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

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

     

    Are both columns Date?

     

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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.

      • scaast's avatar
        scaast
        Frequent Visitor
        9 years ago

        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] )))