cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors