MTD works just for first week

[joshua1990]

Hello everyone!

Somehow the MTD running total is just computing for the first week:

Week	Date	Budget Week	Days per Week	Budget Phasing	Budget WTD	Budget MTD
31	27/07/2020	188334	5	37666,8	37666,8	37666,8
	28/07/2020	188334	5	37666,8	75333,6	75333,6
	29/07/2020	188334	5	37666,8	113000,4	113000,4
	30/07/2020	188334	5	37666,8	150667,2	150667,2
	31/07/2020	188334	5	37666,8	188334	188334
	01/08/2020	188334	5	37666,8		188334
	02/08/2020	188334	5	37666,8		188334
31 Ergebnis		188334	5	37666,8	188334	188334
32	03/08/2020	255000	5	51000	51000	443334
	04/08/2020	255000	5	51000	102000	443334
	05/08/2020	255000	5	51000	153000	443334
	06/08/2020	255000	5	51000	204000	443334
	07/08/2020	255000	5	51000	255000	443334
	08/08/2020	255000	5	51000		443334
	09/08/2020	255000	5	51000		443334
32 Ergebnis		255000	5	51000	255000	443334
33	10/08/2020	255000	5	51000	51000	698334
	11/08/2020	255000	5	51000	102000	698334
	12/08/2020	255000	5	51000	153000	698334
	13/08/2020	255000	5	51000	204000	698334
	14/08/2020	255000	5	51000	255000	698334
	15/08/2020	255000	5	51000		698334
	16/08/2020	255000	5	51000		698334
33 Ergebnis		255000	5	51000	255000	698334
34	17/08/2020	255000	5	51000	51000	953334
	18/08/2020	255000	5	51000	102000	953334
	19/08/2020	255000	5	51000	153000	953334
	20/08/2020	255000	5	51000	204000	953334
	21/08/2020	255000	5	51000	255000	953334
	22/08/2020	255000	5	51000		953334
	23/08/2020	255000	5	51000		953334

As you can see in the last column the MTD measure is just running for the first week.

But why?

These are my measures:

Budget MTD = CALCULATE ( [Budget WTD], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Fiscal Year] = VALUES ( 'Calendar'[Fiscal Year] ) && 'Calendar'[Fiscal Month Number] = VALUES ( 'Calendar'[Fiscal Month Number] ) && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ) 

Budget WTD = CALCULATE ( MAX ( 'Calendar'[Day of Week Number] ) * [Budget Phasing], 'Calendar'[IsWorkingDay] = 1 )

[amitchandak]

@joshua1990 , typically I use datesmtd with a date calendar

refer

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[joshua1990]

@amitchandak thanks for your help.

Since I am working with a fiscal calendar (see measures) where every month has individual start and end dates, the datesmtd function will not work.

I would like to learn and to know why my approach is not working.

[joshua1990 · Accepted solution]

Somehow this approach works:

CALCULATE (SUMX('Calendar',
    [Budget per Day]),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[JnJ Year]
            = VALUES ( 'Calendar'[Fiscal Year] )
            && 'Calendar'[Fiscal Month Number]
                = MAX ( 'Calendar'[Fiscal Month Number] )
            && 'Calendar'[Date]
                <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[IsWorkingDay] = 1
    )
)

What do you say?

[amitchandak]

@joshua1990 , while approach seems fine on first look.  I will such you take the Rank approach, have month rank and day of the month using the start date or month period.

Same how it work for WEEK

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

FY Month means they month do not start on 1st. Year start does not matter in case of datesmtd