Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |