Forum Discussion

cbarker's avatar
cbarker
Frequent Visitor
1 year ago
Solved

Running YTD Budget Total Using Partial Current Month Amount, Previous Month Full Amount

I've got a calculated measure that pulls a true MTD figure based on the current time and then applies the % of the month complete to a whole budget figure. E.g., a full month of 20 days (excluding weekends, holidays) has a $5.0M budget...at 10 days, this budget would reflect $2.5M, and at 20 days would be the whole $5.0M. As an FYI these budget figures are not split down to the day in a calendar/date table as well.

 

This is what I need for the current month to reflect (which is done correctly currently), but I'd like to have a running YTD total in the table where the previous month, which has already been completed, reflects the whole month budget. For example in the image below, the partial budget reflects in the left column correctly, but I'm struggling to develop a measure or calculation to have this running total include the previous month full month figures (on the right) pulled in and summed to have a true daily YTD table to date.

 

Anyone have any guidance here? I was having some success with an IF statement pulling something similar, but couldn't get things to total out correctly. Thanks y'all much in advance!

 

 

MTD Measure:

MTD Bud to Date =
CALCULATE(SUM(Revenue_Raw_Data[GS_Exclfr]),
TREATAS ( VALUES ( CustomerDivisions[Region Mapping] ), Revenue_Raw_Data[True Region]),
TREATAS ( VALUES ( CustomerDivisions[Customer_No] ), Revenue_Raw_Data[Customer_No]),
Revenue_Raw_Data[Fact]="Budget",
MONTH(Revenue_Raw_Data[Date])=MONTH(now()),YEAR(Revenue_Raw_Data[Date])
=YEAR(now()))*[% Network Days of Month until Today (percentage)]
Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    Hi cbarker 

     

    You can get the YTD value before the start of the current month and just add the current MTD to it only if the current row month is the current month

     

    YTD Before Current Month = 
CALCULATE (
    [Total Transactions],
    FILTER (
        ALL ( Dates ),
        Dates[Date] < MIN ( Dates[Date] )
            && Dates[Year] = MAX ( Dates[Year] )
    )
)

     

    YTD Before Current Month + MTD =
VAR TodaysMonth =
    FORMAT ( TODAY (), "yymm" )
VAR CurrentRowMonth =
    FORMAT ( MAX ( Dates[Date] ), "yymm" )
VAR _MTD =
    IF ( TodaysMonth = CurrentRowMonth, [MTD Measure] )
RETURN
    _MTD
        + CALCULATE (
            [Total Transactions],
            FILTER (
                ALL ( Dates ),
                Dates[Date] < MIN ( Dates[Date] )
                    && Dates[Year] = MAX ( Dates[Year] )
            )
        )

     

5 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi cbarker 

     

    You can get the YTD value before the start of the current month and just add the current MTD to it only if the current row month is the current month

     

    YTD Before Current Month = 
CALCULATE (
    [Total Transactions],
    FILTER (
        ALL ( Dates ),
        Dates[Date] < MIN ( Dates[Date] )
            && Dates[Year] = MAX ( Dates[Year] )
    )
)

     

    YTD Before Current Month + MTD =
VAR TodaysMonth =
    FORMAT ( TODAY (), "yymm" )
VAR CurrentRowMonth =
    FORMAT ( MAX ( Dates[Date] ), "yymm" )
VAR _MTD =
    IF ( TodaysMonth = CurrentRowMonth, [MTD Measure] )
RETURN
    _MTD
        + CALCULATE (
            [Total Transactions],
            FILTER (
                ALL ( Dates ),
                Dates[Date] < MIN ( Dates[Date] )
                    && Dates[Year] = MAX ( Dates[Year] )
            )
        )

     

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    Hi cbarker,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Running YTD budget total using partial current month amount.

     

    DAX code

     

     

    Running YTD Budget = 
    VAR CurrentMonth = MONTH(TODAY())
    VAR CurrentYear = YEAR(TODAY())
    VAR IsCurrentMonth = IF(MONTH('Date'[Date]) = CurrentMonth && YEAR('Date'[Date]) = CurrentYear, 1, 0)

     

    RETURN
        CALCULATE(
            SUM('Budget'[Amount]),
            FILTER(
                ALL('Date'),
                ('Date'[Year] < CurrentYear) || 
                ('Date'[Year] = CurrentYear && 'Date'[Month] <= CurrentMonth)
            )
        )

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

     

    Thank you

    • v-dineshya's avatar
      v-dineshya
      Community Support
      1 year ago

      Hi cbarker ,

      If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
      Thanks and Regards

    • v-dineshya's avatar
      v-dineshya
      Community Support
      1 year ago

      Hi cbarker ,

      If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
      Thanks and Regards

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi cbarker ,

    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards