Forum Discussion
Solved
Help needed for month-on-month change visuals
Hi all, I've been working on a dashboard for app usage, which is near completion, but there's just one visual that I can't quite iron out. I'm trying to create a visual showing month-on-month % ...
rather than creating column for % change or %, it is always better to create measure.
try create measure like this
% Diff = Var LM = CALCULATE(SUM(YourTable[InstanceCount]), PARALLELPERIOD(DateTable[DateKey],-1,Month)) VAR CM = SUM(YourTable[InstanceCount]) Return DIVIDE (CM-LM,LM,BLANK())
FarhanAhmed
Community Champion
Community Championrather than creating column for % change or %, it is always better to create measure.
try create measure like this
% Diff =
Var LM = CALCULATE(SUM(YourTable[InstanceCount]), PARALLELPERIOD(DateTable[DateKey],-1,Month))
VAR CM = SUM(YourTable[InstanceCount])
Return
DIVIDE (CM-LM,LM,BLANK())
Brilliant!
I got it working, I got pretty close to your measure, I jsut couldn't figure out how to get a sum of the previous month's data. I didn't even know PARALLELPERIOD existed.
Thankyou so much!Just a quick update, if you're like me and you added in an instance count column to your table, using COUNT works better than SUM:
% Diff = Var LM = CALCULATE(COUNT(YourTable[InstanceCount]), PARALLELPERIOD(DateTable[DateKey],-1,Month)) VAR CM = COUNT(YourTable[InstanceCount]) Return DIVIDE (CM-LM,LM,BLANK())Using SUM can result in wonky percentages since you end up muliplying counts with themselves:
Month
Instance count Row number Sum Jun-23 26,713 26,713 713,584,369 May-23 20,482 20,482 419,512,324 % differenceCOUNT % differenceSUM 30% 70%