Forum Discussion

Retrorock's avatar
Retrorock
Frequent Visitor
4 years ago

Help correcting Custom LYMTD measure

The sample PBIX file can be found here

So due to certain reasons with how the data is and how the customers want to see the data, I have to create a custom MTD/ LYMTD measure base on the periods in sales table

 

I managed to get the MTD working with this measure 

 

 

MTD = CALCULATE(SUM(Sales2[EUR Value]), 
Sales2[Period Rank] = MAX(Sales2[Period Rank]) 
&& Sales2[Inv_Date] <= MAX(Sales2[Inv_Date])      
)

 

 

 

but problems arise with the LYMTD measure

 

 

MTD Last Year V2 = 
VAR lyRank =  MAX(Sales2[Period Rank])-12
VAR result = 
    CALCULATE(SUM(Sales2[EUR Value]), 
        Sales2[Period Rank] = lyRank
        && Sales2[Inv_Date] <= MAX(Sales2[Inv_Date]),
        ALLEXCEPT(Sales2,Sales2[Country],Sales2[Inv_Date])
    )
RETURN 
    result

 

 

 

it can filter down to period and even country correctly, but for some reason it is not reflecting the MTD when filtered to Inv_Date despite it being part of the ALLEXCEPT expression. Can someone help with this issue?

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Retrorock , 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])))

    • Retrorock's avatar
      Retrorock
      Frequent Visitor
      4 years ago

      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