cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Handas
Regular Visitor

TOTALMTD for various month beginning and ending

Dear experts,

 

I am trying to use TOTALMTD function:

MTD Act = TOTALMTD([Sales ACT],'Calendar - all'[Fiscal Date])
 
Handas_2-1660661357019.png

(in printscreen next lvl of Fiscal Period is week)

 
However my months have specific start/end dates. For example February start date should be 29.1. and ending 4.3. How should I solve this? Is it possible to mark column in date table as Month? It seems TOTALMTD calculates month from Fiscal Date and not take column Fiscal Month into consideration.

 

Handas_1-1660661166635.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Handas , Assume you Month Period and period Start date

 

you need period rank on period start date of year period

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

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Handas , Assume you Month Period and period Start date

 

you need period rank on period start date of year period

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

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors