Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
n_huis
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

n_huis_0-1710943353872.png

And the result

n_huis_1-1710943393868.png

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

 

 

 

View solution in original post

1 REPLY 1
amitchandak
Super User
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
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.