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 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.

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

7 Replies

  • 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?

         

         

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    9 years ago

    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.