Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MIKO_CPL
Frequent Visitor

Masure of The difference between individual months for 12 RM

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.

MIKO_CPL_0-1681805496048.png

12 RM Category rate =
IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0),
SUMX(VALUES('Calendar'[Month Yr]), [Pre 12 RM Cat rate]) , --To force the '0' and the division by 12 for the RollBack
[Pre 12 RM Cat rate])

Pre 12 RM Cat 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 Cat_current = CALCULATE([MTD cat rate], 'Calendar'[Current Month])
 
RETURN

IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0),
SUMX(RollBack_Table , [MTD cat rate]) ,
SUMX(RollBack_Table_2,[MTD cat rate]) + Cat_current
)

Do you have any ideas how could I cope with that? 

Thank you in advance!
Mik

2 REPLIES 2
MIKO_CPL
Frequent Visitor

Thank you for your remark. 
The expected outcome is highlighted in green.

Let's assume I've got the below set of data. 

DateNo. of peopleCat 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-2350000015000.303.8020.3003.668
Mar-2340000020000.503.5020.5003.403
Feb-2330000015000.503.0020.500 
Jan-2320000010000.502.5020.500 
Dec-221000005000.502.0020.500 
Nov-225000004500.091.5020.090 
Oct-224000004000.101.4120.100 
Sep-2230000013500.451.3120.450 
Aug-222000003000.150.8620.150 
Jul-221000002500.250.7120.250 
Jun-225000002000.040.4620.040 
May-2240000011500.290.4220.288 
Apr-223000001060.040.1350.035 
Mar-222000001990.100.1000.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' ?

lbendlin
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors