Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBIWhat
Helper I
Helper I

Calculating Rolling Sum - Weird when Blank or February 29th

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 dateSalesRolling SalesExpected ValueMatch
31/01/2016 00:00 6110FALSE
29/02/2016 00:00 6110FALSE
31/03/2016 00:00 6110FALSE
30/04/2016 00:00 6110FALSE
31/05/2016 00:00 6110FALSE
30/06/2016 00:00 6110FALSE
31/07/2016 00:00 6110FALSE
31/08/2016 00:00 6110FALSE
30/09/2016 00:00000TRUE
31/10/2016 00:00000TRUE
30/11/2016 00:00000TRUE
31/12/2016 00:00000TRUE
31/01/2017 00:00000TRUE
28/02/2017 00:00000TRUE
31/03/2017 00:00000TRUE
30/04/2017 00:00000TRUE
31/05/2017 00:00000TRUE
30/06/2017 00:00000TRUE
31/07/2017 00:00000TRUE
31/08/2017 00:00000TRUE
30/09/2017 00:00000TRUE
31/10/2017 00:00000TRUE
30/11/2017 00:00000TRUE
31/12/2017 00:00000TRUE
31/01/2018 00:00000TRUE
28/02/2018 00:00000TRUE
31/03/2018 00:00000TRUE
30/04/2018 00:00000TRUE
31/05/2018 00:00000TRUE
30/06/2018 00:00000TRUE
31/07/2018 00:00000TRUE
31/08/2018 00:00000TRUE
30/09/2018 00:00111TRUE
31/10/2018 00:00122TRUE
30/11/2018 00:00022TRUE
31/12/2018 00:00022TRUE
31/01/2019 00:00133TRUE
28/02/2019 00:00033TRUE
31/03/2019 00:00033TRUE
30/04/2019 00:00033TRUE
31/05/2019 00:00144TRUE
30/06/2019 00:00155TRUE
31/07/2019 00:00122127127TRUE
31/08/2019 00:004131131TRUE
30/09/2019 00:00150281281TRUE
31/10/2019 00:000280280TRUE
30/11/2019 00:001280280TRUE
31/12/2019 00:000280280TRUE
31/01/2020 00:001281281TRUE
29/02/2020 00:000280280TRUE
31/03/2020 00:000280280TRUE
30/04/2020 00:001281281TRUE
31/05/2020 00:000281281TRUE
30/06/2020 00:007287287TRUE
31/07/2020 00:0099385385TRUE
31/08/2020 00:0012275275TRUE
30/09/2020 00:00159430430TRUE
31/10/2020 00:001281281TRUE
30/11/2020 00:000281281TRUE
31/12/2020 00:002282282TRUE
31/01/2021 00:003285285TRUE
28/02/2021 00:000567284FALSE
31/03/2021 00:000284284TRUE
30/04/2021 00:002286286TRUE
31/05/2021 00:000285285TRUE
30/06/2021 00:005290290TRUE
31/07/2021 00:0026309309TRUE
22/08/2021 00:0011221221TRUE

 

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,

1 ACCEPTED SOLUTION
PowerBIWhat
Helper I
Helper I

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

View solution in original post

4 REPLIES 4
PowerBIWhat
Helper I
Helper I

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
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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:
 
As I say much further away. Can you see why this isn't working? I really seem to struggle with which dates to choose.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.