Forum Discussion
dynamic running difference
I want to calculate the difference between current value and previous value dynamically based on date hierarchy , how to acheive same in power bi?
Example
Scenario 1: if i use year and sales then below should be the output
Scenario 2: if i use year, month and sales, then below should be the output
2 Replies
powerbiexpert22 , We got visual calculation function in Feb release. Try previous
Master Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8LRelease Notes: https://powerbi.microsoft.com/en-us/blog/visual-calculations-preview/
Microsoft Documentation: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overviewOr checkout offset
Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U
Hi amitchandak ,
we cannot use visual calculations since it is in preview mode, I am able to calculate previous sales with below logic however if i sort the month then previous value is showing blank for month
Level = SWITCH(TRUE(),ISINSCOPE('CALENDAR'[Month]),"Month",ISINSCOPE('CALENDAR'[Year]),"Year","All")Previous Sales:
Prev Sales =IF([Level]="Year" ,CALCULATE([Sales],OFFSET(-1,All('CALENDAR'[Year]))),IF([Level]="Month",CALCULATE([Sales],OFFSET(-1,All('CALENDAR'[Month])))))
