Forum Discussion
Helper IIIDAX to find Previous Period
l would appreciate it if somebody could help with the following problem.
I have a date table that includes a custom period for a group of selected dates.
I can easily calculate the current period by looking up the period name against today's date.
I am struggling to find a DAX calculation that will display the previous period.
Here is a snippet from my date table:
The periods vary in the number of days allocated to each period.
There are 12 periods in any financial year.
The Financial Year could include data from more than one calendar year.
Every row in the date table includes period assigned to that day.
Is there a way to show:
When the date is in Period 04-05-01, the previous period would be 03-04-12
When the date is in period 04-05-02, the previous period would be 04-05-01
etc etc
Any assistance would be appreciated as this is driving me crazy.
Kind regards
Mark
msommerf , Make sure you have a separate period table(Say Date), Then create a rank column on period
Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense) //YY-MM format column in your case
Then create measure like
example
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))Last year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Period] = Max('Date'[Period])))
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
5 Replies
ryan_mayuSuper User
you can create a column in the datetime table. please see the attachment below.
period&year = VAR _date=DATEADD('Table'[Date],-1,MONTH) return if(ISBLANK(_date),blank(),maxx(FILTER('Table','Table'[Date]=_date),'Table'[Fiscalyear])&"-"&maxx(FILTER('Table','Table'[Date]=_date),'Table'[Period]))- msommerf
Many thanks for responding. I have applied your suggestion but the results are not as expected. My periods do not align to any months:
- amitchandak
msommerf , Make sure you have a separate period table(Say Date), Then create a rank column on period
Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense) //YY-MM format column in your case
Then create measure like
example
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))Last year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Period] = Max('Date'[Period])))
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA- msommerf
Thank you so much for responding.
When I attempt to create a ranking for the period table all records come out with a ranking of 1.
