Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

MTD Running Total Sales

Hi all,   I have already posted regarding this problem and never really found a solution so I am going to rephrase hoping someone out there can help.   I am trying to build measures allowing to g...
Need Help
  • popov's avatar
    popov
    8 years ago

    Correct formula

     

    Prev MTD Running Total Sales =
    VAR CurrentDayNumber =
        MIN ( MAX ( YTDInfos[CurrentDayNumber] ); MAX ( 'Calendar'[DayNumber] ) )
    RETURN
        IF (
            [Prev MTD Sales] <> BLANK ();
            CALCULATE (
                [Total Sales];
                FILTER (
                    ALL ( 'Calendar' );
                    AND ( 'Calendar'[MonthOffset] = -12; 'Calendar'[DayNumber] <= CurrentDayNumber )
                )
            )
        )

popov's avatar
popov
Icon for Resolver III rankResolver III
8 years ago

Hello!
Try this formulas:

 

MTD Running Total Sales =
IF (
    [MTD Sales] <> BLANK ();
    CALCULATE (
        [Total Sales];
        FILTER (
            ALL ( 'Calendar' );
            'Calendar'[MonthOffset] = 0
                && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
)

 

 

Prev MTD Running Total Sales =
VAR CurrentDayNumber =
    MAX ( YTDInfos[CurrentDayNumber] )
RETURN
    IF (
        [Prev MTD Sales] <> BLANK ();
        CALCULATE (
            [Total Sales];
            FILTER (
                ALL ( 'Calendar' );
                AND ( 'Calendar'[MonthOffset] = -12; 'Calendar'[DayNumber] <= CurrentDayNumber )
            )
        )
    )

popov's avatar
popov
Icon for Resolver III rankResolver III
8 years ago

Sorry, Prev MTD Running Total Sales is not correct. I will post correct formula later

  • popov's avatar
    popov
    Icon for Resolver III rankResolver III
    8 years ago

    Correct formula

     

    Prev MTD Running Total Sales =
    VAR CurrentDayNumber =
        MIN ( MAX ( YTDInfos[CurrentDayNumber] ); MAX ( 'Calendar'[DayNumber] ) )
    RETURN
        IF (
            [Prev MTD Sales] <> BLANK ();
            CALCULATE (
                [Total Sales];
                FILTER (
                    ALL ( 'Calendar' );
                    AND ( 'Calendar'[MonthOffset] = -12; 'Calendar'[DayNumber] <= CurrentDayNumber )
                )
            )
        )

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you so much, this works beautifully!

       

      Have a great day :)

      • popov's avatar
        popov
        Icon for Resolver III rankResolver III
        8 years ago

        Glad to help you.