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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DomA001
Regular Visitor

DAX HELP, show last 12 periods based on slicer selections

Hi,

 

I want to show previous 12 "periods" based on slicer selections. So, for example if I select "Period" = 9 and "Year" = 2020 I want it to show me 2019 P9, 2019 P10, 2019 P11, 2019 P12, 2020 P1, 2020 P2, 2020 P3, 2020 P4, 2020 P5, 2020 P6, 2020 P7, 2020 P8. 

 

What's the best way to implent this? 

2 REPLIES 2
wdx223_Daniel
Super User
Super User

i think the best way is to set up your date table, then use time intellegence function
amitchandak
Super User
Super User

@DomA001 , Ideally these Period P1 , p2 should p01 and p02 .

Then you can have rank on YYYYPP - 2020P01 And have separate period year table

 

Period Rank = RANKX(all('Date'),'Date'[Year Period ],,ASC,Dense)
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))
Last year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=(max('Date'[Period Rank]) -12)))

 

last 12 month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]>=(max('Date'[Period Rank]) -12) && 'Date'[Period Rank]<=max('Date'[Period Rank])))

 

But this might group the things together, refer thsi video for the approch

https://www.youtube.com/watch?v=duMSovyosXE

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors