Calculating Rolling Sum - Weird when Blank or February 29th

[PowerBIWhat]

Hi,

I have data that has two columns one the end of the month and the other number of sales. I want to be able to sum the last 12 months of sales (including the current month). I have a DAX measure that works except when sales are blank (only in months where the asset wasn't trading) or February following a leap year. In the blank example it sums every sales value, in the 28th Feb after a Leap year e.g. 28/02/21, it sums every value up to that date. I can't work out why but sure it is something similar.

Sales rolling 12month total =

IF(

    ISFILTERED('Sales'[Period end date]),

    VAR __LAST_DATE = ENDOFMONTH('Sales'[Period end date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'Sales'[Period end date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),

            ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))

        )

    RETURN

        SUMX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('Sales'),

                    'Sales'[Period end date].[Year],

                    'Sales'[Period end date].[QuarterNo],

                    'Sales'[Period end date].[Quarter],

                    'Sales'[Period end date].[MonthNo],

                    'Sales'[Period end date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(

                SUM('Sales'[NumberofSales]),

                ALL('Sales'[Period end date])

            )

        )

)

Here is the current output, with the two columns on the left after i have exported to excel to check. Expected value is what i am expecting to see.

Period end date	Sales	Rolling Sales	Expected Value	Match
31/01/2016 00:00		611	0	FALSE
29/02/2016 00:00		611	0	FALSE
31/03/2016 00:00		611	0	FALSE
30/04/2016 00:00		611	0	FALSE
31/05/2016 00:00		611	0	FALSE
30/06/2016 00:00		611	0	FALSE
31/07/2016 00:00		611	0	FALSE
31/08/2016 00:00		611	0	FALSE
30/09/2016 00:00	0	0	0	TRUE
31/10/2016 00:00	0	0	0	TRUE
30/11/2016 00:00	0	0	0	TRUE
31/12/2016 00:00	0	0	0	TRUE
31/01/2017 00:00	0	0	0	TRUE
28/02/2017 00:00	0	0	0	TRUE
31/03/2017 00:00	0	0	0	TRUE
30/04/2017 00:00	0	0	0	TRUE
31/05/2017 00:00	0	0	0	TRUE
30/06/2017 00:00	0	0	0	TRUE
31/07/2017 00:00	0	0	0	TRUE
31/08/2017 00:00	0	0	0	TRUE
30/09/2017 00:00	0	0	0	TRUE
31/10/2017 00:00	0	0	0	TRUE
30/11/2017 00:00	0	0	0	TRUE
31/12/2017 00:00	0	0	0	TRUE
31/01/2018 00:00	0	0	0	TRUE
28/02/2018 00:00	0	0	0	TRUE
31/03/2018 00:00	0	0	0	TRUE
30/04/2018 00:00	0	0	0	TRUE
31/05/2018 00:00	0	0	0	TRUE
30/06/2018 00:00	0	0	0	TRUE
31/07/2018 00:00	0	0	0	TRUE
31/08/2018 00:00	0	0	0	TRUE
30/09/2018 00:00	1	1	1	TRUE
31/10/2018 00:00	1	2	2	TRUE
30/11/2018 00:00	0	2	2	TRUE
31/12/2018 00:00	0	2	2	TRUE
31/01/2019 00:00	1	3	3	TRUE
28/02/2019 00:00	0	3	3	TRUE
31/03/2019 00:00	0	3	3	TRUE
30/04/2019 00:00	0	3	3	TRUE
31/05/2019 00:00	1	4	4	TRUE
30/06/2019 00:00	1	5	5	TRUE
31/07/2019 00:00	122	127	127	TRUE
31/08/2019 00:00	4	131	131	TRUE
30/09/2019 00:00	150	281	281	TRUE
31/10/2019 00:00	0	280	280	TRUE
30/11/2019 00:00	1	280	280	TRUE
31/12/2019 00:00	0	280	280	TRUE
31/01/2020 00:00	1	281	281	TRUE
29/02/2020 00:00	0	280	280	TRUE
31/03/2020 00:00	0	280	280	TRUE
30/04/2020 00:00	1	281	281	TRUE
31/05/2020 00:00	0	281	281	TRUE
30/06/2020 00:00	7	287	287	TRUE
31/07/2020 00:00	99	385	385	TRUE
31/08/2020 00:00	12	275	275	TRUE
30/09/2020 00:00	159	430	430	TRUE
31/10/2020 00:00	1	281	281	TRUE
30/11/2020 00:00	0	281	281	TRUE
31/12/2020 00:00	2	282	282	TRUE
31/01/2021 00:00	3	285	285	TRUE
28/02/2021 00:00	0	567	284	FALSE
31/03/2021 00:00	0	284	284	TRUE
30/04/2021 00:00	2	286	286	TRUE
31/05/2021 00:00	0	285	285	TRUE
30/06/2021 00:00	5	290	290	TRUE
31/07/2021 00:00	26	309	309	TRUE
22/08/2021 00:00	11	221	221	TRUE

I am learning as i go so please try and keep as simple as possible. Explanations to why it is happening + the solution would be appreciated.

Thanks in advance,

[PowerBIWhat · Accepted solution]

For anybody who stumbles on this and wants to know the solution. I fixed it with the following measure:

Sales rolling 12month total =
IF(ISBLANK(SUM('Sales'[NumberofSales])),
0,
VAR __LAST_DATE = ENDOFMONTH('Date table'[FullDate])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Sales'[Period end date],
STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),
ENDOFMONTH(DATEADD(__LAST_DATE, 0, MONTH))
)
RETURN
SUMX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Sales'),
'Sales'[Period end date].[Year],
'Sales'[Period end date].[QuarterNo],
'Sales'[Period end date].[Quarter],
'Sales'[Period end date].[MonthNo],
'Sales'[Period end date].[Month],
'Sales'[Period end date].[Day]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Sales'[NumberofSales]),
ALL('Sales'[Period end date])
)
)
)

[parry2k]

@PowerBIWhat very hard to say what is not working and what you are looking for. put together a sample pbix with expected output and share it using one drive/google drive, it shouldn't be this complicated. LOTS of resources/posts are already done on this topic.

[parry2k]

@PowerBIWhat when working with dates, it is a best practice to add a date dimension in your model and then work from there. You can add one in your model by following my blog post here  Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

Once you have a date table, there are a lot of posts adding a rolling sum and just google for it but the key here is to have a date table in your model.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PowerBIWhat]

Hi,

I've struggled to get it to work with the date table too. I have one in place but thought it may be easier for somebody to assist on the version which is all in the same table.

I seem to be a lot further away in the date table version of the measure. It is only summing the current month.

The measure is:

Flag Move Out rolling 12month total 2 =

VAR NumOfMonths = 12

VAR LastCurrentDate =

MAX ('Date table'[FullDate])

VAR Period =

DATESINPERIOD ('Date table'[FullDate], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

CALCULATE (

SUMX (

VALUES ('Date table'[CalendarYearMonth]),

SUM('Sales'[Number of Sales])

),

Period

)

VAR FirstDateInPeriod = MINX ( Period,'Date table'[FullDate] )

VAR LastDateWithSales = MAX ( 'Sales'[Period end date] )

RETURN

IF ( FirstDateInPeriod <= LastDateWithSales, Result )

I took this from the following website:

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

As I say much further away. Can you see why this isn't working? I really seem to struggle with which dates to choose.