TOTALMTD for various month beginning and ending

[Handas]

Dear experts,

I am trying to use TOTALMTD function:

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

(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.

[amitchandak · Accepted solution]

@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