Total for last 30 days when no entries for particular dates
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.
