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

[Swinden]

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.

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

Any tips?

[selimovd]

Hey @Swinden ,

can you share your file?

Usually that shouldn't happen.

Best regards

Denis

[Swinden]

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

[selimovd]

Hey @Swinden ,

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

Best regards

Denis

[selimovd · 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:

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Swinden]

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!

[selimovd]

Hey @Swinden ,

I'm happy it works now 😊

Have a great day.

Best regards

Denis