I am stuck on an issue where I would want to show counts for a categorical field based on the till date Date column as per the selected slicer:
Input:
Consider 2 tables:
First: table named Table
ID Rule Start Date End Date Year Month Uniq
1 A 01-01-2020 01-01-2020 FY20 1 FY20_1
2 B 01-01-2020 01-02-2020 FY20 1 FY20_1
2 C 01-01-2020 01-03-2020 FY20 1 FY20_1
1 A 01-01-2020 01-02-2020 FY20 1 FY20_1
2 B 01-02-2020 01-02-2020 FY20 2 FY20_2
3 C 01-02-2020 01-04-2020 FY20 2 FY20_2
4 D 01-02-2020 01-03-2020 FY20 2 FY20_2
5 E 01-02-2020 01-03-2020 FY20 2 FY20_2
2 B 01-03-2020 01-03-2020 FY20 3 FY20_3
3 C 01-03-2020 01-03-2020 FY20 3 FY20_3
4 D 01-03-2020 01-04-2020 FY20 3 FY20_3
5 E 01-03-2020 01-04-2020 FY20 3 FY20_3
6 E 01-03-2020 NaN FY20 3 FY20_3
7 E 01-03-2020 NaN FY20 3 FY20_3
Second: Table named Calendar_Table (Uniq column is the common key)
Year Month Uniq
FY20 1 FY20_1
FY20 2 FY20_2
FY20 3 FY20_3
Problem Statement:
I have 2 slicers Year and Month coming from the Calendar _Table. Based on the selected Month and Year combination, I want to show the break down of counts of the ID column from the Table table. However the counts should not be filtered for that specific month, rather until that month(like a YTD). Also there is a condition that I need to consider here.
For a specific month selected, take all rows until that month based on
Start Datecolumn, and filterEnd Datefor only next month onward (filter out all till that month) , this will give 4 rows for February.
Example: If Slicer for Month 2 is selected , i would select the first 8 rows from Start Date (Jan and Feb) and then filter from End Date any date after Feb(Match,April,so on..) , then i would like to show the distribution of Rule based on count of ID column.
Current try: A measure that gets the start date based on the Month selected:
StartDt = VAR test_var = MAX('Table'[Uniq])
return CALCULATE(FIRSTNONBLANK('Table'[Start Date],1),FILTER(Calendar_Table,Calendar_Table[Uniq]=test_var))
Then apply the condition explained above:
YTD_RULE_Breakup = var stdt = [StartDt]
return CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Start Date]<=stdt && 'Table'[End Date]>stdt))
Current Output:
When I pull this in a card, i get the correct value (4) , however I am not able to get the distribution of the Rule based on this measure:
Expected output:
My expected output is :
I would appreciate if someone can help me doing this.

