Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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 @Anonymous ,

 

can you share your file?

Usually that shouldn't happen.

 

Best regards

Denis

Anonymous
Not applicable

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

Hey @Anonymous ,

 

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

 

Best regards

Denis

Hey @Anonymous ,

 

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
 
Anonymous
Not applicable

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 @Anonymous ,

 

I'm happy it works now 😊

Have a great day.

 

Best regards

Denis

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors