Search code examples
excelpowerbidaxpowerpivot

DAX: Using Calendar Month as a column and as a criteria for counting records based on current row

So I have the following pivot table report through my data model. I want my measure 'Branches Per Cluster' to consider the current column of month or year.

enter image description here

I have the following tables aside from a generated calendar table, these two below are related by 'CODE'.

A dim table named 'Branch Profiles'

CODE AREA CLUSTER DATE OPENED
AAA Area 1 Cluster 1 01/05/1990
AAB Area 1 Cluster 1 05/03/2022
ABA Area 2 Cluster 1 01/03/2005
BAA Area 3 Cluster 2 01/03/2024

A fact table named 'BasicData'

CODE Volume Value Date
AAA 1000 10000 06/01/1990
AAB 2000 20000 06/01/2020
ABA 3000 30000 06/01/2005
BAA 4000 40000 06/01/2008

This is what I currently have for my Branches Per Cluster measure which might be obvious for experienced users that is syntactically wrong though I believe it shows what I was trying to do as I'm not quite sure how to reference the column as a filter. Basically, I just want to count the Branches ("CODE") for the specific area that have a date opened before the month specified by the column filters.

=CALCULATE(
    DISTINCTCOUNT('Branch Profiles'[CODE]), 
    ALLEXCEPT('Branch Profiles', 
        'Branch Profiles'[AREA], 
        'Branch Profiles'[CODE]
    ),
    YEAR('Branch Profiles'[DATE STARTED]) <= 'Calendar'[Year],
    MONTH('Branch Profiles'[DATE STARTED]) <= 'Calendar'[Month Number]
 )
Solution

  • Here is the proper code for the Measure that I was trying to accomplished. What I was missing earlier was a way to reference the current value of the Year and Month columns (Context Filters) and use it as a criteria on the [Date Started] column to get the count on [CODES] that are opened on or before currently specified Month or Year.

    If this was done in PowerBI the solution would have involved the function named "SELECTEDVALUE" however, the logic can still be implemented in Excel through the longer syntax shown below in my measure.

        VAR cur_year =
        IF (
            HASONEVALUE('Calendar'[Year]),
            VALUES('Calendar'[YEAR])
        )
    
    VAR cur_month =
        IF (
            HASONEVALUE('Calendar'[Month]),
            VALUES('Calendar'[Month Number])
        )

    RETURN
        IF(
            NOT ISBLANK([Send Volume]),
            CALCULATE(
                DISTINCTCOUNT(Branch Profiles[CODE]), 
                ALLEXCEPT('Branch Profiles', 
                    'Branch Profiles'[AREA], 
                    'Branch Profiles'[CODE]
                ),
                'Branch Profiles'[DATE STARTED] < DATE(cur_year, cur_month + 1, 1)
            )
        )