Reply
Retrorock
Frequent Visitor
Partially syndicated - Outbound

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

Syndicated - Outbound

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)