Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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])
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.
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])
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
really appreciate, thank you so much it works smoothly
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |