MTD Running Total Sales

[jlouvel]

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 give me MTD Running Total Sales for the current fiscal year and the previous one.

To do so, I came up with the following measures:

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

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

When looking at the result through a Table, including Fiscal Year, Month Number and Day Number, everything seems to work just fine:

Now, when removing the Fiscal Year and the Month Number from the table, here's what I am expecting to get:

But here's what I am getting:

This is driving me crazy as I have been stuck with this for quite some time and still cannot figure out what is wrong!!!

Any help would be greatly appreciated.

I can share a pbix file if necessary.

Thanks!

[jlouvel]

The expected behavior is actually the following...

Sorry for the inconvenience.

[v-ljerr-msft]

Hi @jlouvel,

Could you share a sample pbix file which can reproduce the issue, so that we can do some tests on it and better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[jlouvel]

Below a link to a pbix file illustrating the problem.

RunningTotalProblemSample.pbix

Thanks.

[popov]

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]

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

[popov · Accepted solution]

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

[jlouvel]

Thank you so much, this works beautifully!

Have a great day 🙂

[popov]

Glad to help you.