topic Re: Rolling 12 months with empty values included in DAX Commands and Tips

Rolling 12 months with empty values included

MiTorres76 — Wed, 06 Dec 2023 09:23:02 GMT

hi!

I am trying to use below DAX to calculate rolling 12 months. it works fine if there is value, but doesn't work if there is no value. I want that the DAX even calculate with empty values. how can i do that? exemple: the result for 202310 should be 7,17 and NOT 7,82

Thanks!!

Re: Rolling 12 months with empty values included

123abc — Thu, 07 Dec 2023 09:33:38 GMT

To calculate a rolling 12-month total in DAX while including empty values, you can modify your DAX formula to use a combination of the DATESBETWEEN, CALCULATETABLE, and SUMX functions. Here's an example DAX formula that you can use:

Rolling12Months =
CALCULATE(
SUMX(
DATESBETWEEN(
'YourTable'[Date],
TODAY() - 12 * 30, -- Adjust the number of days based on your data frequency
TODAY()
),
'YourTable'[YourMeasure] -- Replace 'YourTable' and 'YourMeasure' with your actual table and measure names
),
ALL('YourTable'[Date])
)

This formula calculates the sum of the specified measure ('YourMeasure') for the rolling 12-month period using DATESBETWEEN. The ALL('YourTable'[Date]) part ensures that the context of the date filter is removed, allowing the calculation to include empty values.

Make sure to replace 'YourTable' and 'YourMeasure' with the actual names of your table and measure. Adjust the number of days in the DATESBETWEEN function based on your data frequency (e.g., if your data is at a monthly frequency, use 12; if it's at a daily frequency, use 365).

Note: The exact implementation may depend on your data model and specific requirements, so feel free to adapt the formula accordingly.

Re: Rolling 12 months with empty values included

ray_codex — Thu, 07 Dec 2023 10:00:51 GMT

Hi, your code looks good enough, I think you are only missing the blank values in [Antal mp heltapp]. So I would suggest adjusting that part to:

CALCULATE ( AVERAGEX ( VALUES ( DimDate[Period] ), IF ( ISBLANK ( [Antal mp heltapp] ), 0, [Antal mp heltapp] ) ),

To explain, you are iterating every item in the values column, but if [Antal mp heltapp] is blank the whole item is skipped. So if you have 30 items in the values column that have only 29 corresponding non blank in [Antal mp heltapp], it will give you the total of [Antal mp heltapp] over values, divided by 29 in stead of 30. This explains exactly why your result is higher than it actually is.

Re: Rolling 12 months with empty values included

MiTorres76 — Thu, 07 Dec 2023 12:50:06 GMT

hi ray_codex!
Thanks for your reply, but after making those changes it didn't turn out what I was hoping for. I get the same result as Count of Mätpunktsnummer. The average of 202309 is correct as it takes the sum of 202309-202210 = 90/12 = 7.5.
the average of 202310 should be
202310-202211 =86/12 = 7.17

and so on

Re: Rolling 12 months with empty values included

MiTorres76 — Thu, 07 Dec 2023 12:59:43 GMT

hi 123abc!
thanks for your reply, but with your DAX I get the sum of the last 12 months. I would like to calculate the average. for example the average for 202309 is correct as it takes the sum of 202309-202210 = 90/12 = 7.5.
the average of 202310 should be 202310-202211 =86/12 = 7.17