Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'd like to insert a column with calculated difference between the previous months and 'current' months.
The difference should base on the values from 12 RM measure as indicated below.
Thank you in advance!
Mik
Thank you for your remark.
The expected outcome is highlighted in green.
Let's assume I've got the below set of data.
Date | No. of people | Cat I vol. | Category I rate [%] | 12 RM category I rate [%] | 12 RM category I rate vs 1 month [%] | 12 RM category I rate vs 1 year [%] |
Apr-23 | 500000 | 1500 | 0.30 | 3.802 | 0.300 | 3.668 |
Mar-23 | 400000 | 2000 | 0.50 | 3.502 | 0.500 | 3.403 |
Feb-23 | 300000 | 1500 | 0.50 | 3.002 | 0.500 | |
Jan-23 | 200000 | 1000 | 0.50 | 2.502 | 0.500 | |
Dec-22 | 100000 | 500 | 0.50 | 2.002 | 0.500 | |
Nov-22 | 500000 | 450 | 0.09 | 1.502 | 0.090 | |
Oct-22 | 400000 | 400 | 0.10 | 1.412 | 0.100 | |
Sep-22 | 300000 | 1350 | 0.45 | 1.312 | 0.450 | |
Aug-22 | 200000 | 300 | 0.15 | 0.862 | 0.150 | |
Jul-22 | 100000 | 250 | 0.25 | 0.712 | 0.250 | |
Jun-22 | 500000 | 200 | 0.04 | 0.462 | 0.040 | |
May-22 | 400000 | 1150 | 0.29 | 0.422 | 0.288 | |
Apr-22 | 300000 | 106 | 0.04 | 0.135 | 0.035 | |
Mar-22 | 200000 | 199 | 0.10 | 0.100 | 0.100 |
Where:
Category I vol > volume of I category people from total
Catgory I rate = (category I vol / No. of people) * 100
12 RM category I rate = Category I month + 12 RM category I rate (prev. month)
12 RM category I rate vs 1 month = 12 RM category I rate (current month) - 12 RM category I rate (prev. month)
12 RM category I rate vs 1 year = 12 RM category I rate (current month and year) - 12 RM category I rate (current month prev. year)
--DAX equivalent to 12 RM Category I rate [%]
12 RM category I rate =
IF (
AND (
ISFILTERED ( 'Calendar'[Month Yr] ),
DATEDIFF ( MAX ( 'Calendar'[Dates] ), TODAY () - 1, MONTH ) <> 0
),
SUMX ( VALUES ( 'Calendar'[Month Yr] ), [Pre 12 RM cat I rate] ),
[Pre 12 RM cat I. rate]
)
---Pre 12 RM cat I. rate measure
Pre 12 RM cat I rate =
VAR RollBack_Table =
CALCULATETABLE (
FILTER (
DATESINPERIOD ( 'Calendar'[Dates], MAX ( 'Calendar'[Dates] ), - 12, MONTH ),
'Calendar'[Dates] = EOMONTH ( 'Calendar'[Dates], 0 )
),
'Calendar'[Flag_Projec]
)
VAR RollBack_Table_2 =
CALCULATETABLE (
FILTER (
DATESINPERIOD ( 'Calendar'[Dates], MAX ( 'Calendar'[Dates] ), - 11, MONTH ),
'Calendar'[Dates] = EOMONTH ( 'Calendar'[Dates], 0 )
),
'Calendar'[Flag_Projec]
)
VAR Churn_current =
CALCULATE ( [MTD c rate], 'Calendar'[Current Month] )
RETURN
IF (
AND (
ISFILTERED ( 'Calendar'[Month Yr] ),
DATEDIFF ( MAX ( 'Calendar'[Dates] ), TODAY () - 1, MONTH ) <> 0
),
SUMX ( RollBack_Table, [MTD c rate] ),
--On force les 0 et la division par 12 pour le RollBack
SUMX (
RollBack_Table_2,
[MTD c rate]
) + Churn_current
)
Could you please give me a hint how could I calculate in DAX '12 RM category I rate vs 1 month' ?
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |