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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous , 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

@Anonymous , 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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