Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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,
Solved! Go to 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])
)
)
)
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])
)
)
)
@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.
@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:
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |