I am trying to work out how to compute the data for R12 and it is really bothering me that I cannot get to the right answer.
I have a set of data covering 24 months 01.06.2020 - 31.05.2022 and I want to look at this in 13 blocks of rolling 12 data. i.e. block 1 = 01.06.2020 to 31.05.2020, block 2 = 01.07.2020 to 30.06.2020 and so on until block 13 = 01.06.2021 to 31.05.2022.
I have all the data in excel and it works fine but the spreadsheet is massive and clunky and I want to migrate to Power BI.
I have managed to get so far but am stuck as follows.
The measure for R12 available hours is
Total Available Hours R12 =
CALCULATE(
[Available Hours],
FILTER (
ALL (Dates),
AND (
Dates[Date] <= MAX (Dates[Date]),
DATEADD (
Dates[Date],
1,
YEAR
) > MAX ( Dates[Date] )
)
)
And this returns the following graph and calculations:
The data matches for the period I am interest for the first 8 rolling 12 blocks to 31.12.2021. But for Jan 22 to May 22 it is wrong and is ignoring the data for 2022. For example, Jan 22 is only 01.02.21 to 31.12.2021, Feb 22 is only 01.03.21 to 31.12.2021. The calculation is just stopping at 31.12.2021 and I do not understand why?
The second issue is how to get the calculation to work just for the 13 blocks of R12 data. I know that I can do this with a filter and select the blocks I want but as I change the report each month the date range will change and wondered if it was possible?
I know I am close to the answer as the first 8 months work but just cannot figure out the reason.
I am new to DAX and am struggling to make the jump from Excel (which I am fairly well competent in) to DAX.
As for your second question you could use a “Relative date” filter on Dates[Date] or if you are using the Extended Date table M function use Offsets to filter to a specific period only.
I have uploaded my model and you will see that I have now 3 versions of the formula.
V2 works with the
Total Available Hours R12 v2 =
CALCULATE (
[Available Hours],
DATESINPERIOD (
‘Dates’[Date],
MAX ( ‘Dates’[Date] ),
-1,
YEAR
)
)
But this looks at the Dates Table which is just a table that has dates 01.01.2019 to 31.12.2022.
I think this explains why the R12 blocks extend from 31.05.2022.
Because of that I used the dates table in the invoked function which create a table for just the dates I require 01.06.2020 to 31.05.2022 but weirdly it does not add the months cumulatively but only shows separately. I think I will still have the problem with the 11 months before but I am wondering why V2 works but V3 doesn’t when the only difference appears to be the dates table??
I have attached my newer model with V2 and V3 in it as well
Hi.
Going through your question and the solution that have been provided thus far, I’m just add to it to get the result that you want. I just added what I called “the protection layer” to restrict the values to only show where the 12 months roll to a complete 12 month.
Total Available Hours R12 (Complete) =
--- Core Formula Starts ---
VAR EndDate = MAX( Dates[Date] )
VAR MonthsToRoll = 12
VAR Result =
CALCULATE(
[Available Hours],
DATESINPERIOD( Dates[Date], EndDate, - MonthsToRoll, MONTH )
)
--- Core Formula Ends ---
--- Protection Layer ---
VAR CurrentMinDate = MIN( Dates[Date] )
VAR MaxLabourDate = CALCULATE( MAX( Labour_data[Date] ), REMOVEFILTERS( ) )
VAR MinLabourDate = CALCULATE( MIN( Labour_data[Date] ), REMOVEFILTERS( ) )
VAR MonthsFromMinLabourDate =
CALCULATE(
COUNTROWS( VALUES( Dates[Month & Year] ) ),
DATESBETWEEN( Dates[Date], MinLabourDate, EndDate )
)
RETURN
IF(
EndDate <= MaxLabourDate
&& CurrentMinDate >= MinLabourDate
&& MonthsFromMinLabourDate >= MonthsToRoll,
Result
)
I have noticed that in the second solution with the protection layer that you have added variables (VAR)
If I wanted to apply this code to other table columns and not just “Available Hours” would it still work or would the VAR Result get confused because there would be more than one.
Or does that VAR restrict itself into the code for that measure only if that makes sense?
The Variables (VAR) in the protection layer section are computing different dates to determine when the 12M rolling should be computed. To help understand this you can RETURN each of those variables to see what the output is.
Regarding reusing this for a different measure. It should work as long the calculation for the new measure is also from the labour_data table. The bit that would need to change is the measure reference in line 7.
Thanks for this - this has been working brilliantly and i have managed to get the Power BI to match my spreadsheets at a total level.
However, when I filter on employee so that I can see an individuals stats the Power BI breaks. This appears to be for individuals who have not got a full 24 months of data.
Once this is completed this will be awesome and will allow me to use this model for group and individual appraisals with the click of a filter change. Very excited.
I wonder if the R12 formula somehow needs to create a nil where there is a null or something but I do not know how to achieve this unless I add all the job groups for every employee for the 24 month range as I have the unavailable data to bring in a default of zero for all possible groups for each employee
Hi @NeilR1
This is an easy fix. The code I pasted in this message thread was slightly different from what was in the pbix. What you just need to change is in line 15. Instead of this:
Right. Some more time understanding the DAX fundamentals would go a long way in being able to solve more problems.
Also, I saw you applied the R12 calculation to a lot of other measures. You may want to explore Calculation Groups as that would help greatly to maintain the formulas and be able to apply it to numerous other measures.
