Calculating MTD on a custom Calendar

[bradscot]

Hey everyone,

I'm having issues calculating MTD on a custom calender. For example the sales month for january is jan 5 2022 - feb 1st. I have create a custom calender and assigned Financial Month, Days, & Years to the dates in the year. Then I am using filters to narrow down these date ranges. It is working however the input for this is the selling date and if there isn't a sale on the date then it won't show the MTD sales for the salesperson. It just leaves a blank instead of showing the sales accumulated up to the current/selected date. What I need is for it to show the current total MTD for the sales person up to the selected date. This is my current Measure.

MTD SALES 3.0 =

VAR CurrentFinMonth = SELECTEDVALUE('Sales Matrix'[Fin Month])

VAR CurrentFinYear = SELECTEDVALUE('Sales Matrix'[FinYear])

VAR CurrentDay = SELECTEDVALUE('Sales Matrix'[Day Number])

VAR SellingDate = HASONEVALUE('Sales Matrix'[Selling Date])

RETURN

CALCULATE( COUNT(MOCK_DATA[VIN]),

FILTER(ALL('Sales Matrix'),'Sales Matrix'[Fin Month] = CurrentFinMonth && 'Sales Matrix'[FinYear] = CurrentFinYear && 'Sales Matrix'[Day Number] <= CurrentDay && SellingDate = TRUE

))

Selling Date	Fin Month	Fin Year	Day Number
1/5/2022	1	2022	1
1/6/2022	1	2022	2

SalePersonID	Sales Date	VIN
1112	1/5/2022	1x
1113	1/5/2022	2b
1112	1/7/2022	3n
1113	1/8/2022	4c

The issue being that if i flip to the Jan 6th in my selling date slicer, the MTD measure will show blank on my table vizualization instead of carrying over the sale from the previous day. If I change the slicer to the 7th, the table will show 2 sales for salespersonID 1112, and blank for 1113. and then if i change the date to 1/8 it will show two sales for 1113 and blank for 1112. 

Any Help would be appreciated. I've seen similar issues but nothing that directly solves my problem. Thanks!

[ValtteriN · Accepted solution]

Hi,

In my experience a simple CALCULATE + DATESMTD should do this if you use the financial month as the axis value.
Example:
Data:

My "financial month" in this example is MONTH('Calendar'[date]+10)

Dax:

CustomMonthMTD = CALCULATE(SUM('Sales'[Value]),DATESMTD('Calendar example'[Date]))

End result:

As we can see the MTD calculation is done using the financial month instead of the date.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!