cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Union And Summarize Table

Hi guys,

 

for some reason the last summarize is not showing up!

Any ideas?

 

DAX 1.png

 

thank you

 

John

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Union And Summarize Table

@johnghannam

 

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.


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: Union And Summarize Table

@johnghannam

 

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.


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Frequent Visitor

Re: Union And Summarize Table

It does have contiguous series Smiley Sad

 

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

 

Highlighted
Community Champion
Community Champion

Re: Union And Summarize Table

@johnghannam

 

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 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors