Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago

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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

 

Release 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-overview

 

 

Or checkout offset

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago

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]))
)))