Power BI DAX for sub category

[XinfulTraveller]

I am having a dataset at the below

Month	Week Number	Userid	Ratings
Dec-21	W52	94	Disagree
Dec-21	W53	95	Strongly Agree
Dec-21	W54	96	Neutral
Dec-21	W55	97	Agree
Dec-21	W56	98	Neutral
Dec-21	W57	99	Agree
Jan-22	W01	100	Strongly Agree
Jan-22	W01	101	Strongly Agree
Jan-22	W01	102	Neutral
Jan-22	W01	103	Strongly Agree
Jan-22	W01	104	Agree
Jan-22	W01	105	Agree
Jan-22	W01	106	Strongly Disagree
Jan-22	W01	107	Neutral
Jan-22	W02	108	Strongly Agree
Jan-22	W02	109	Strongly Agree
Jan-22	W02	110	Strongly Agree
Jan-22	W02	111	Strongly Agree
Jan-22	W02	112	Neutral
Jan-22	W02	113	Strongly Disagree
Jan-22	W02	114	Disagree
Jan-22	W02	115	Agree
Jan-22	W02	116	Strongly Agree
Jan-22	W02	117	Neutral
Jan-22	W03	118	Strongly Agree
Jan-22	W03	119	Strongly Agree
Jan-22	W03	120	Agree
Jan-22	W03	121	Agree

I have a Slicer in PBI. When W03 is selected, my ideal output is at the below

Values	Count
Strongly Agree	2
Agree	2
Neutral	0
Disagree	0
Strongly Disagree	0

Jan-23 MTD based on W03 selection

MTD - Jan	Count
Strongly Agree	10
Agree	5
Neutral	4
Disagree	1
Strongly Disagree	2

I came up with a DAX formula at the below

CSAT_Delivery_Count_MTD =

var SelectedDate=CALCULATE(MAX(FACT_Responses[CAL_WEEK_END_DT]),FACT_Responses[Year Week]=SELECTEDVALUE(FACT_Responses[Year Week]))

VAR DATEFILTER= DATESBETWEEN(

   FACT_Responses[CAL_WEEK_END_DT],

calculate(MIN(FACT_Responses[CAL_WEEK_END_DT]),all(FACT_Responses),FACT_Responses[Month]=SELECTEDVALUE(FACT_Responses[Month]),FACT_Responses[Year]=SELECTEDVALUE(FACT_Responses[Year])),

    SelectedDate)

return

CALCULATE(    [CSAT_Delivery_Count],ALL(FACT_Responses),DATEFILTER,VALUES(FACT_Responses[CSAT_Delivery]))

However it seems that I could not get the MTD if the selected weeks has count by each category = 0

this was my result which is wrong

MTD - Jan	Count
Strongly Agree	10
Agree	5
Neutral	0
Disagree	0
Strongly Disagree	0

Can advise how to resolve this?

[XinfulTraveller]

i am sorry but it is not working for me.

It is not returning any values

CALCULATE([CSAT_Delivery_Count],DATESMTD(DIM_Date[CAL_WEEK_END_DT]))

[ValtteriN]

Hi,

For MTD do this:

1. create a calendar table and relationship to you fact table:

2.  create MTD measure like this:

Rating_MTD = CALCULATE([Measure 43],DATESMTD('Calendar'[Date]))
(The measure 43 is just COUNT of the UserID table)

3. End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/