Running total for previous month stops at the last day of current month

[jori]

Hi guys,

I have the following measure to calculate the running total for the previous month:

CALCULATE (
        [_UmsatzTotal],
        DATESMTD (
            DATEADD ( Datumstabelle[FullDateAlternateKey], -1, MONTH )
        )
    )

It worked fine in January, but in February, the running total only goes up to the 28th.

I found another way to do it, but I still have the same issue:

CALCULATE(
    [_UmsatzTotal],
    FILTER(
        ALL( Datumstabelle ),
        Datumstabelle[Monat Relativ] = MAX( Datumstabelle[Monat Relativ] ) + 1
        && Datumstabelle[Day] <= MAX ( Datumstabelle[Day] )
    )
)

It always takes the Day value in my date table for the current month which only goes up to 28 in February.

Does anyone have an idea?

Thank you!

[jori · Accepted solution]

Unfortunately, this does not calculate the running total.

I actually found a way though:

Instead of having the current month in the x-axis, I keep the date fixed on January of this year.

Then I just change the relative month in the measure based on the difference to January (it's negative for me because the relative date for the past is a positive value in my model):

VAR Relativ = MONTH ( TODAY () ) - 2

RETURN
CALCULATE(
    [_UmsatzTotal],
    FILTER(
        ALL( Datumstabelle ),
        Datumstabelle[Monat Relativ] = MAX( Datumstabelle[Monat Relativ] ) - Relativ
        && Datumstabelle[Day] <= MAX ( Datumstabelle[Day] )
    )
)

[v-xinc-msft]

Hi @jori ,

You can use the EOMONTH function to get the last day of the previous month and then calculate the summary from the first to the last day of the previous month.

CALCULATE(
    [_UmsatzTotal],
    DATESBETWEEN(
        Datumstabelle[FullDateAlternateKey],
        EOMONTH(TODAY(), -2) + 1,
        EOMONTH(TODAY(), -1)
    )
)

You could adjust the date filter to ensure that no dates are missed when working with February.

CALCULATE(
    [_UmsatzTotal],
    FILTER(
        ALL(Datumstabelle),
        Datumstabelle[Monat Relativ] = MAX(Datumstabelle[Monat Relativ]) - 1
        && Datumstabelle[Day] <= MAX(Datumstabelle[Day])
    )
)

If the above codes still not help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Petition for all months to have the same number of days! 🙂

It is what it is.  The time intelligence functions have to perform contorsions when it comes to "same day last month"  at the end of each month.  There is no real "fix"  for that.