Help correcting Custom LYMTD measure

[Retrorock]

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?

[amitchandak]

@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]

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