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

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

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

Oh so you got it working ok?