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
NourJ
Helper III
Helper III

Difference between current month and previous month

Dear Community,

 

I want to calculate the difference in percentage between the current and previous month  / and the current and before the previous / and the current and the quarter

 

my table is as the following:

 

Table: Append1

Date column: Month name

Value as measure: IDP Arrived IND 2018+2019

 

thank you in advance,

 

Nour

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

Hi @NourJ 

Assume "before the previous" refers to total before previous month,

let to say, current month is 5, "before the previous" should be value in 1,2,3 month.

 

Assume "the quarter" refers to the quarter of current month

let to say, current month is 5, "the quarter" refers to quarter (month 4,5,6)

 

If I understand correctly, create such measures

current = [IDP Arrived IND 2018+2019]

previous month = CALCULATE([current],FILTER(ALL(Append1),Append1[Month name]=MAX(Append1[Month name])-1))

difference(Current-previous) = [current]-[previous month]

diff/current = [difference(Current-previous)]/[current]

before the previous = SUMX(FILTER(ALL(Append1),Append1[Month name]<MAX(Append1[Month name])),[current])

quarter no 1 = SWITCH(Append1[Month name],1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)
//this is a calcutaed column instead of a measure, others are measures
the quarter = SUMX(ALLEXCEPT(Append1,Append1[quarter no 1]),[current])

5.png

As for percentage, you could do the meausre like [measure1]/[measure2] or divide([measure1],[measure2])

Then change the data format to percentage type.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @NourJ 

Assume "before the previous" refers to total before previous month,

let to say, current month is 5, "before the previous" should be value in 1,2,3 month.

 

Assume "the quarter" refers to the quarter of current month

let to say, current month is 5, "the quarter" refers to quarter (month 4,5,6)

 

If I understand correctly, create such measures

current = [IDP Arrived IND 2018+2019]

previous month = CALCULATE([current],FILTER(ALL(Append1),Append1[Month name]=MAX(Append1[Month name])-1))

difference(Current-previous) = [current]-[previous month]

diff/current = [difference(Current-previous)]/[current]

before the previous = SUMX(FILTER(ALL(Append1),Append1[Month name]<MAX(Append1[Month name])),[current])

quarter no 1 = SWITCH(Append1[Month name],1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4)
//this is a calcutaed column instead of a measure, others are measures
the quarter = SUMX(ALLEXCEPT(Append1,Append1[quarter no 1]),[current])

5.png

As for percentage, you could do the meausre like [measure1]/[measure2] or divide([measure1],[measure2])

Then change the data format to percentage type.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Oh Sorry, i just discover a problem which is i want to show the quarter as card but it display the wrong number it should show 95 Capture.PNG

 

really appreciate, thank you so much it works smoothly

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.