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

TOTALYTD Excluding items when they don't occur in a given month

TOTALYTD is not giving the result i would expect it to.  When i write this measure;

 

COUNT YTD = TOTALYTD(COUNT(Sheet1[BRAND]),Sheet1[MONTH])
 
It gives me the correct total YTD for my data, but when i introduce the Brand dimension to a matrix table (or any table) it does not show the count for a brand that didn't occur in the selected month
 
Example data below.  The BRAND/MONTH columns are what is being summarized in .pbix.  As you can see, there are 0 instances of "FRUIT LOOPS" in February 2020.  The total YTD of all brands should be 19.
Swinden_0-1622476110420.png

 

 When i interpret this in Power BI, it gives the correct total, but excludes Fruit Loops as there was no instances in February
Swinden_1-1622476196235.png

 

Any tips?

 
1 ACCEPTED SOLUTION

Hey @Swinden ,

 

the problem is or was that you don't use a proper date table.

Most of the time intelligence functions work only if you have a date table. I integrated a date table and now it works like a charm:

selimovd_0-1622492746007.png

 

I will send you the file in private.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

6 REPLIES 6
selimovd
Super User
Super User

Hey @Swinden ,

 

can you share your file?

Usually that shouldn't happen.

 

Best regards

Denis

Swinden
Frequent Visitor

Long time reader, first time poster... dumb question, but how can I share .pbix?

Hey @Swinden ,

 

you have to use an external service like OneDrive or I prefer swisstransfer.com

 

Best regards

Denis

Hey @Swinden ,

 

the problem is or was that you don't use a proper date table.

Most of the time intelligence functions work only if you have a date table. I integrated a date table and now it works like a charm:

selimovd_0-1622492746007.png

 

I will send you the file in private.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

Swinden
Frequent Visitor

Thank you!  I had to change some relationships and measures, but it works when i add the data table and made a few other tweaks. Thanks so much for your help!

Hey @Swinden ,

 

I'm happy it works now 😊

Have a great day.

 

Best regards

Denis

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors