topic Re: Total for last 30 days when no entries for particular dates in DAX Commands and Tips

Total for last 30 days when no entries for particular dates

Coriel-11 — Fri, 05 May 2023 16:01:18 GMT

I have a table which is essentially like the one below. Each time one gives us a "Sentiment" of Positive/Neutral/Negative/Unclassified that sentiment is added as a new line, along with the date.

I have dates that go back to the start of the year so I want to record how many of each category have been recorded over the previous 30 days (including the day in question), so I can produce 100% stacked chart like this:

So I created this measure:

Sentiment MMT = CALCULATE( Count(Sentiment[Sentiment]), DATESINPERIOD( 'Sentiment'[Date], MAX('Sentiment'[Date]), -1,MONTH ) )

The problem I'm having is that if on one of the days it happens that no-one recorded a particular sentiment, then rather than counting up all entries for the other 29 days, it just returns a blank – you can see wome exmaples in the bottom right where there are no grey values.

An example of the data (which doesn't correspond precisely to the chart , it's just to give you an idea) is below.

Does anyone have any ideas?

Date	Sentiment
30-Apr-23	Neutral
30-Apr-23	Positive
30-Apr-23	Unclassified
30-Apr-23	Positive
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Positive
29-Apr-23	Positive
29-Apr-23	Positive
29-Apr-23	Positive
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Neutral
29-Apr-23	Positive
29-Apr-23	Positive

etc.

Re: Total for last 30 days when no entries for particular dates

Fowmy — Fri, 05 May 2023 21:42:14 GMT

Coriel-11

Create a DATES table, Link the dates table to your fast table using a one-to-many relation and modify your calculation. Make sure you use the Date column from your Dates table in the Visual. Create a Copy of the Dates table for date selection as well.

Here is the modified formula:

Sentiment MMT = VAR __DateSelected = SELECTEDVALUE('Date Selector'[Date]) VAR __CurrentDate = SELECTEDVALUE('Dates'[Date]) VAR __Period = DATESINPERIOD( 'Dates'[Date], __CurrentDate, 1, MONTH ) Return IF( __DateSelected IN __Period, CALCULATE( Count(Sentiment[Sentiment]), __Period ) )

File is attached

Re: Total for last 30 days when no entries for particular dates

Coriel-11 — Fri, 05 May 2023 22:04:40 GMT

Thank you!!. I had tried that, but for some reason I hadn't added the date table date to the visual's X axis, so I'd disregarded that approach and gone back to trying to do something more Dax-y.

Much appreciated. It was driving me mad!

Matt