Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Union And Summarize Table

Hi guys,   for some reason the last summarize is not showing up! Any ideas?     thank you   John    
Tips and Tricks
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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.

OwenAuger's avatar
OwenAuger
Super User
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

It does have contiguous series :smileysad:

 

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