topic Re: TOTALYTD Excluding items when they don't occur in a given month in DAX Commands and Tips

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

Anonymous — Mon, 31 May 2021 15:50:21 GMT

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?

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

selimovd — Mon, 31 May 2021 16:35:00 GMT

Hey Anonymous ,

can you share your file?

Usually that shouldn't happen.

Best regards

Denis

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

Anonymous — Mon, 31 May 2021 19:32:37 GMT

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

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

selimovd — Mon, 31 May 2021 19:58:00 GMT

Hey Anonymous ,

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

Best regards

Denis

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

selimovd — Mon, 31 May 2021 20:26:39 GMT

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:

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

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

Anonymous — Mon, 31 May 2021 22:18:02 GMT

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!

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

selimovd — Tue, 01 Jun 2021 05:58:04 GMT

Hey Anonymous ,

I'm happy it works now 😊

Have a great day.

Best regards

Denis