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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Retrorock
Frequent Visitor

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?

2 REPLIES 2
amitchandak
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors