cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Lagged data one fiscal period

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.

1 ACCEPTED SOLUTION
Super User

@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

Super User

@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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors