Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a lot of calculation in a report which calculate stats for the "last [period of time]". Now that we have started a new year, a lot of these calculations seem broken.
I thought that these calcs would work in the new year because Dax's date/calendar math appears capable of handling math that spans different years and months. For instance, if you ask it for a date - 1 month and it the input as january, it correctly returns December. But this does not appear to be the case here.
Below you see a table detailing the numbers for certain periods of time. X1 is the average size of the population, X2 is the size of the losses in that period of time, and Ann Rate is the annualized rate of loss in that period. The periods in question are for the selected period (PRD) via a slicer, Month to Date, Year to Date, Last Month, Last Year, End of Month, and End of Year.
Here is the code for x1, the population of a given data set:
Population_LM = -- STATIC CALC VAR LMDs = [LM_Days] -- Correctly retruns the number of days in the last month VAR DN = [DNOW] -- DNOW being the current date RETURN ROUND(CALCULATE(SUM('Population[COUNT]) , CALCULATETABLE('Population' , MONTH('BI-DATE_DIM'[DIM_DATE]) = (MONTH(DN) - 1) && YEAR('BI-DATE_DIM'[DIM_DATE]) = YEAR(DN) ) ) / LMDs, 0)
x2, the count of losses:
Loss_LM = -- STATIC CALC VAR DN = [DNOW] RETURN CALCULATE(COUNT('Losses'[ID]) , CALCULATETABLE('Losses' , (MONTH('BI-DATE_DIM'[DIM_DATE]) = MONTH(DN) - 1) && (YEAR('BI-DATE_DIM'[DIM_DATE]) = YEAR(DN)) ) )
And Ann Rate is the annualized rate of loss, based on the calendar year:
TO_LM_ANN = ([Loss_LM]/[Population_LM]) * ([EOYrDay_LM] / [LM_Days]) -- STATIC calc. Works for all other metrics but this one. -- [EOYrDay_LM] correcly returns the number of days for the year of which last month was a part.
In other words, the rate for the given period of time * the number of days in the year / the length of the period is the annualized rate. This calculation works for all other metrics, so the problem must be that the code doesn't know how to calculate stats for last month when the last month was in another year.
TABLES
+Sample data for @v-yuezhe-msft
Population is a table which for simplicity's sake has counts contributing to a 'census', if you will.
As you can see, the total population for December was 50, and the population this far in January is 60.
Count | Date |
15 | 12/1/2018 |
16 | 12/15/2018 |
19 | 12/30/2018 |
35 | 1/1/2019 |
25 | 1/9/2019 |
Losses is just a table detailing events related to that population.
As you can see, there were 10 in December and 6 so far in January.
ID | Date |
Ashely | 12/4/2018 |
Naida | 12/6/2018 |
Violet | 12/13/2018 |
Pa | 12/14/2018 |
Alline | 12/16/2018 |
Jesusa | 12/16/2018 |
Lucrecia | 12/18/2018 |
Bronwyn | 12/20/2018 |
Gene | 12/21/2018 |
Velda | 12/26/2018 |
Ken | 1/1/2019 |
Rossie | 1/1/2019 |
Olevia | 1/3/2019 |
Patricia | 1/5/2019 |
Carylon | 1/9/2019 |
Bob | 1/10/2019 |
BI-DATE_DIM is simple a calendar table. It has 1:* relationships with the other tables with dates. You don't need to know why this is, but it is necesary.
DIM_DATE |
12/1/2018 |
12/2/2018 |
12/3/2018 |
12/4/2018 |
12/5/2018 |
12/6/2018 |
12/7/2018 |
12/8/2018 |
12/9/2018 |
12/10/2018 |
12/11/2018 |
12/12/2018 |
12/13/2018 |
12/14/2018 |
12/15/2018 |
12/16/2018 |
12/17/2018 |
12/18/2018 |
12/19/2018 |
12/20/2018 |
12/21/2018 |
12/22/2018 |
12/23/2018 |
12/24/2018 |
12/25/2018 |
12/26/2018 |
12/27/2018 |
12/28/2018 |
12/29/2018 |
12/30/2018 |
12/31/2018 |
1/1/2019 |
1/2/2019 |
1/3/2019 |
1/4/2019 |
1/5/2019 |
1/6/2019 |
1/7/2019 |
1/8/2019 |
1/9/2019 |
1/10/2019 |
Given these numbers, we would expect to get:
LM = 10 / 50 * 365 / 31 = 235%
Why not 20%? because I am required to use annualized numbers.
Solved! Go to Solution.
Ok I figured it out. It's actually pretty simple; you just explicitely tell PBI what to do in January:
Population_LM = VAR LMDs = [LM_Days] VAR DN = [DNOW]
VAR MDN = MONTH(DN)
VAR YDN = YEAR(DN) RETURN ROUND( DIVIDE( CALCULATE( SUM('Population'[COUNT]) , CALCULATETABLE('Population' , MONTH('BI-DATE_DIM'[DIM_DATE]) = IF(MDN = 1 , 12 , MDN ) && YEAR('BI-DATE_DIM'[DIM_DATE]) = IF(MONTH(DN) = 1 , YDN - 1 , YDN ) ) ) , LMDs ) , 0 )
and,
Loss_LM = -- STATIC CALC VAR DN = [DNOW]
VAR MDN = MONTH(DN)
VAR YDN = YEAR(DN) RETURN CALCULATE( COUNT('Losses'[ID]) , CALCULATETABLE(Losses' , MONTH('BI-DATE_DIM'[DIM_DATE]) = IF(MDN = 1 , 12 , MDN ) && YEAR('BI-DATE_DIM'[DIM_DATE]) = IF(MDN = 1 , YDN - 1 , YDN ) ) )
I still think those PREVIOUS...() functions don't really work as envisioned.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |