Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All
Need to calculate percentage variance of year to month of sum of values for two columns for eg. col A, col B with year being selected from the slicer for specific period of time like from JAN to JULY .
Ex: In Sep 2017, Year of Month should provide change in % in Aug 2017 as against % change from Jan 2017 till Jul 2017. The whole DAX expression I need to calculate in the Direct Query mode. There are other filters also like Country and Region. Year to month are calculated over two columns as shown below
Sample data
Region | Country | Date Field | Col A | Col B |
AMERICAS | US | 1-Jan-17 | 59 | 96 |
AMERICAS | US | 15-Jan-17 | 21 | 33 |
AMERICAS | US | 21-Jan-17 | 32 | 57 |
AMERICAS | US | 1-Feb-17 | 18 | 70 |
AMERICAS | US | 2-Feb-17 | 84 | 92 |
AMERICAS | US | 5-Feb-17 | 93 | 93 |
AMERICAS | US | 11-Mar-17 | 87 | 94 |
AMERICAS | US | 21-Mar-17 | 63 | 91 |
AMERICAS | US | 31-Mar-17 | 34 | 63 |
EMEA | FR | 1-Jan-17 | 10 | 74 |
EMEA | FR | 15-Jan-17 | 95 | 98 |
EMEA | FR | 21-Jan-17 | 53 | 75 |
EMEA | FR | 1-Feb-17 | 82 | 93 |
EMEA | FR | 2-Feb-17 | 77 | 82 |
EMEA | FR | 5-Feb-17 | 90 | 93 |
EMEA | FR | 11-Mar-17 | 46 | 84 |
EMEA | FR | 21-Mar-17 | 28 | 87 |
EMEA | FR | 31-Mar-17 | 10 | 12 |
EMEA | IT | 1-Jan-17 | 32 | 34 |
EMEA | IT | 15-Jan-17 | 12 | 53 |
EMEA | IT | 21-Jan-17 | 30 | 74 |
EMEA | IT | 1-Feb-17 | 47 | 90 |
EMEA | IT | 2-Feb-17 | 40 | 67 |
EMEA | IT | 5-Feb-17 | 76 | 89 |
EMEA | IT | 11-Mar-17 | 67 | 98 |
EMEA | IT | 21-Mar-17 | 54 | 55 |
EMEA | IT | 31-Mar-17 | 68 | 69 |
And
Country = US |
MoM % variance For Feb = ((195/ 255) - (112/186))/ (195/ 255) |
MoM % variance For Mar = ((184/ 248) - (195/ 255))/ (184/ 248) |
Year to Month when on Apr = ( Jan to Feb )-( Mar)/(Jan-to-Feb) = (((307/441) - (184/ 248)))/ (307/441) |
I came up with this :-
YTD =
CALCULATE (
[change],
FILTER (
ALL ( 'Table A'[Date] ),
'Table A'[ Date] = MAX ( 'Table A'[Date] )
&&
'Table A'[Date] <= MAX ( 'Table A'[Date] )
where
change =
KEEPFILTERS(VALUES('Table A'[ total %YTM])),
CALCULATE(SUM('Table A'[total %])) - SUMX(Table A, Table A[total %YTM])
)
)
Solved! Go to Solution.
HI @rahul2,
Please refer to below steps if it suitable for your requirement:
1. Summary original table.
Summary Table = SELECTCOLUMNS(SUMMARIZE(Sheet3,Sheet3[Region],Sheet3[Country],Sheet3[Date Field].[Year],Sheet3[Date Field].[MonthNo],"ColA",SUM(Sheet3[Col A]),"ColB",SUM(Sheet3[Col B])),"Region",[Region],"Country",[Country],"YM",[Date Field].[Year]&"/"&[Date Field].[MonthNo],"ColA",[ColA],"ColB",[ColB])
2. Add calculate column to calculat the MOM % diff.
MoM % Diff = var CM_Percent=[ColA]/[ColB] var C_Date=DATEVALUE([YM]) var P_Date=FORMAT(DATE(YEAR(C_Date),MONTH(C_Date)-1,1),"YYYY/M") var PM_Percent=LOOKUPVALUE('Summary Table'[ColA],'Summary Table'[YM],P_Date,'Summary Table'[Country],[Country],'Summary Table'[Region],[Region])/LOOKUPVALUE('Summary Table'[ColB],'Summary Table'[YM],P_Date,'Summary Table'[Country],[Country],'Summary Table'[Region],[Region]) return DIVIDE(CM_Percent-PM_Percent,CM_Percent,0)
3. Add calcualte column to calcualte the Rolling % diff.
Rolling % Diff = var R_A=SUMX(FILTER(ALL('Summary Table'),DATEVALUE([YM])<DATEVALUE(EARLIER([YM]))&&[Region]=EARLIER([Region])&&[Country]=EARLIER([Country])),[ColA]) var R_B=SUMX(FILTER(ALL('Summary Table'),DATEVALUE([YM])<DATEVALUE(EARLIER([YM]))&&[Region]=EARLIER([Region])&&[Country]=EARLIER([Country])),[ColB]) var R_Diff=R_A/R_B return DIVIDE(R_Diff-[ColA]/[ColB],R_Diff,0)
Variable prefix: C= current, CM= current month, P = previous, PM= previous month, R= rolling
Regards,
Xiaoxin Sheng
HI @rahul2,
Please refer to below steps if it suitable for your requirement:
1. Summary original table.
Summary Table = SELECTCOLUMNS(SUMMARIZE(Sheet3,Sheet3[Region],Sheet3[Country],Sheet3[Date Field].[Year],Sheet3[Date Field].[MonthNo],"ColA",SUM(Sheet3[Col A]),"ColB",SUM(Sheet3[Col B])),"Region",[Region],"Country",[Country],"YM",[Date Field].[Year]&"/"&[Date Field].[MonthNo],"ColA",[ColA],"ColB",[ColB])
2. Add calculate column to calculat the MOM % diff.
MoM % Diff = var CM_Percent=[ColA]/[ColB] var C_Date=DATEVALUE([YM]) var P_Date=FORMAT(DATE(YEAR(C_Date),MONTH(C_Date)-1,1),"YYYY/M") var PM_Percent=LOOKUPVALUE('Summary Table'[ColA],'Summary Table'[YM],P_Date,'Summary Table'[Country],[Country],'Summary Table'[Region],[Region])/LOOKUPVALUE('Summary Table'[ColB],'Summary Table'[YM],P_Date,'Summary Table'[Country],[Country],'Summary Table'[Region],[Region]) return DIVIDE(CM_Percent-PM_Percent,CM_Percent,0)
3. Add calcualte column to calcualte the Rolling % diff.
Rolling % Diff = var R_A=SUMX(FILTER(ALL('Summary Table'),DATEVALUE([YM])<DATEVALUE(EARLIER([YM]))&&[Region]=EARLIER([Region])&&[Country]=EARLIER([Country])),[ColA]) var R_B=SUMX(FILTER(ALL('Summary Table'),DATEVALUE([YM])<DATEVALUE(EARLIER([YM]))&&[Region]=EARLIER([Region])&&[Country]=EARLIER([Country])),[ColB]) var R_Diff=R_A/R_B return DIVIDE(R_Diff-[ColA]/[ColB],R_Diff,0)
Variable prefix: C= current, CM= current month, P = previous, PM= previous month, R= rolling
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |