CODEWITHSUNDEEP
powerbidax
anky
anky

Reputation: 75100

Count a categorical field in a YTD manner - PowerBI DAX

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 Date column, and filter End Date for 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:

enter image description here

Expected output:

My expected output is :

enter image description here

I would appreciate if someone can help me doing this.

Upvotes: 2

Views: 533

Answers (1)

ffffrank
ffffrank

Reputation: 544

On the YTD_RULE_Breakup measure, try replacing FILTER(ALL('Table') with:

FILTER(ALLEXCEPT('Table','Table'[Rule])

While the FILTER(ALL) works succesfully if we are only interested in the grand total. We can use ALLEXCEPT with the [Rule]-column, to make sure the measure will count the measure for each Rule-column also.

Upvotes: 3

Related Questions