Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful 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
Super 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
    Impactful 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]))
    )))