DAX MTD calculation not working for me

[jeffw14]

Trying to get MTD revenue by RevenueType by calcualting a MTD running total of the daily revenue column. Each RevenueType has a daily revenue amount and I would like a column showing the running month to date total for each of the revenue types. My DAX statement now just gives me the revenue for that day and not the month to date. I have a Date table linked to ShipmentDate in my RevenueProjection Table. 

MTDRevenue = CALCULATE(sum(RevenueProjection[DailyRevenue]), DATESMTD('Date'[Date]))

ShipDate	RevenueType	Year	Quarter	Month	Day	DailyRevenue	MTDRevenue
1/1/2020 0:00	COMPANY TRUCK	2020	Qtr 1	January	1	7,676.97	7,676.97
1/1/2020 0:00	INDEPENDENT CONTRACTOR	2020	Qtr 1	January	1	660.00	660.00
1/2/2020 0:00	COMPANY TRUCK	2020	Qtr 1	January	2	38,774.94	38,774.94
1/2/2020 0:00	INDEPENDENT CONTRACTOR	2020	Qtr 1	January	2	58,764.09	58,764.09
1/2/2020 0:00	PARTNER CARRIER	2020	Qtr 1	January	2	65,078.96	65,078.96
1/3/2020 0:00	COMPANY TRUCK	2020	Qtr 1	January	3	51,907.90	51,907.90
1/3/2020 0:00	INDEPENDENT CONTRACTOR	2020	Qtr 1	January	3	96,967.84	96,967.84
1/3/2020 0:00	PARTNER CARRIER	2020	Qtr 1	January	3	52,615.96	52,615.96
1/4/2020 0:00	COMPANY TRUCK	2020	Qtr 1	January	4	11,978.06	11,978.06
1/4/2020 0:00	INDEPENDENT CONTRACTOR	2020	Qtr 1	January	4	21,161.87	21,161.87
1/4/2020 0:00	PARTNER CARRIER	2020	Qtr 1	January	4	14,058.99	14,058.99

 

[littlemojopuppy]

De nada.  Glad I could help!

[jeffw14]

Yep, that was it, sorry, I reused the same name instead of creating a new measure, it's working now, thanks. 

[jeffw14]

https://www.dropbox.com/s/vsh7y4beyvqcdgs/Sylectus_Revenue_Capacity_Jeff_ds%20%281%29.pbix?dl=0

[littlemojopuppy · Accepted solution]

You created it as a calculated column.  Note the icon in front of DailyRev compared to MTDRevenue

[littlemojopuppy]

Can you share the PBIX?

[jeffw14]

Yes, how do I attach the file?

[littlemojopuppy]

Drop it into Google Drive or DropBox and share the link

[jeffw14]

Yes and Yes, here is the DAX that creates the Date Table

Date =

ADDCOLUMNS (

CALENDAR (DATE(2015,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

[jeffw14]

It is a measure and it doesn't increment as MTD for some reason

[littlemojopuppy]

Do you have a date table in your data model?  And is it marked as a date table?

[littlemojopuppy]

Hi!

Assuming you have a date table and it is marked as such, the code for a MTD calculation is as follows:

TOTALMTD(
	[Measure Name],
	DateTable[Date]
)

You shouldn't create a calculated column for it...just create a measure.  As you use the measure in visualizations filter context will automatically take care of filtering for revenue type, year/month, etc. 

[jeffw14]

My Daily Revenue is a calcualted column and not a measure so it will not work in the [Measure Name], can I just use SUM there to aggregate the DailyRevenue column? Or do I need to create a measure to sum the daily revenue, which technically is already aggregated by day?

[littlemojopuppy]

Do this...

TOTALMTD(
	SUM(DailyRevenue),
	DateTable[Date]
)

[jeffw14]

That still shows the MTD as the same number as the DailyRevenue

ShipDate RevenueType Date DailyRevenue MTDRevenue
1/1/2020 0:00 COMPANY TRUCK 1/1/2020 0:00 7,676.97 7,676.97
1/1/2020 0:00 INDEPENDENT CONTRACTOR 1/1/2020 0:00 660.00 660.00
1/2/2020 0:00 COMPANY TRUCK 1/2/2020 0:00 38,774.94 38,774.94
1/2/2020 0:00 INDEPENDENT CONTRACTOR 1/2/2020 0:00 58,764.09 58,764.09
1/2/2020 0:00 PARTNER CARRIER 1/2/2020 0:00 65,078.96 65,078.96

[littlemojopuppy]

Don't create a calculated column.  Create it as a measure and use the measure in visualizations.