Lagged data one fiscal period

[n_huis]

I created a measure to lag a quantity by one fiscal period and getting an odd result for the previous period. I have fiscal year, fiscal period and fiscal period sequence from a data table. And quantity from a seperate table.

Here is the measure dax

And the result

The quantity of 533 is the issue. Not sure where that is comming from and how to get rid of it.

[amitchandak · Accepted solution]

@n_huis , hope it sequence is build on YYYYPP format

Usually we create a rank column on  YYYYPP or period start date

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

And an have measure like
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))

Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM

You can consider offset

Previous Net Sales = CALCULATE([Net Sales],
OFFSET(-1,ALL('FY Period'[Fiscal Period Key],'FY Period'[Fiscal Period],'FY Period'[Fiscal Year]),
ORDERBY('FY Period'[Fiscal Period Key])))

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

Consider new visual calculation previous

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