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.

3 Replies

  • 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

       

      • OwenAuger's avatar
        OwenAuger
        Super User
        9 years ago

        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 :)