Union And Summarize Table

[Anonymous]

Hi guys,

for some reason the last summarize is not showing up!

Any ideas?

thank you

John

[OwenAuger · Accepted solution]

@Anonymous

The 3rd SUMMARIZE appears to be returning a blank row, suggesting that 30 Jan 2017 is not in the DimDates table.

Does your date table contain a contiguous series of dates?

I tested with a dummy date table ending with 30 Apr 2017 (created with CALENDAR function) and the  DAX query returned three rows.

[Anonymous]

It does have contiguous series

I ended up using the following dax instead

EVALUATE
Union 
(	
	
	SUMMARIZE
	(
		DimDates
		,"Date Reference","MTD"
		,"Date",LASTDATE(DimDates[Dates])
	)
,	

	SUMMARIZE
	(
		DimDates
		,"Date Reference","Last Three Months" 
		,"Date", EOMONTH(Date(Year(LASTDATE(DimDates[Dates])),Month(LASTDATE(DimDates[Dates]))-3,Day(LASTDATE(DimDates[Dates]))),0)
	)	
)	

is this the best practice?

Am I supposed to have my dimdates by day?

My Dates are being generated by sql server the source of the dates is the FactTable that has only the end of the month dates.

Do you think if i switched the dates to the begining of the month the dateadd function will work?

Thanks

[OwenAuger]

@Anonymous

Yes best practice for a date dimension table is to have a row per day for a contiguous block of days.

Your original DAX query would have worked if you had used month-start dates, due to the way DATEADD works.

• DATEADD will follow the lineage of the column provided in the first argument, and only return dates that exist in that column.
• Also by the looks of it DATEADD will not interpret a single date as a month-end date (it will interpret 2+ dates as a sequence of month-end dates).

Your new method works because EOMONTH doesn't require the resultant date to be present in the Date column.

Regards,

Owen 🙂