Forum Discussion
Help correcting Custom LYMTD measure
Super UserRetrorock , For such things what I do
We need year/FY and period , But in a separate date/period table
New columns
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Period Day =datediff([Period Start date] , [Date],Day) +1
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))
PTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))
if year has 12 months or 13 month subract that
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-12 && [Period Day] <=max([Period Day])))
or
LYMTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'), 'Date'[Year]=max('Date'[Year]) && 'Date'[Period]=max('Date'[Period]) && 'Date'[Day of Period] <= Max('Date'[Day of Period])))
Hi amitchandak
I actually saw your guide on custom periods here and it was one of the solutions I already tried beforehand.
In fact the current measure i used was kinda modified off that.
It's not quite obvious from the sales table, however it doesn't quite work for this data because some of the periods differ by country. So for example for 202105 the period for Crossbell and Italy starts on the 4/29, while for Erebonia and Australia that period starts on 4/30 and it could end on different days too.
With periods like these I couldn't create a date / period table that shows this difference while still keeping period / date as a unique key for relationships. This was the reason I used the periods and dates in the sales table itself to make the calculations.
I updated the PBIX file with a Period by country table to better display what I mean