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
rahul2
New Member

Need to calculate percentage variance of year to month

 

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])

)
)

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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])

8.PNG

 

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)

9.PNG

 

Variable prefix: C= current, CM= current month, P = previous, PM= previous month, R= rolling

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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])

8.PNG

 

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)

9.PNG

 

Variable prefix: C= current, CM= current month, P = previous, PM= previous month, R= rolling

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.