Count of IDs by Year and Month – Visual Showing Blank Values

[SBC]

Description:

I have a dataset with the following columns:

ID Created Date Closed Date

1235634	12/25/2020 3:15:30 PM	01/02/2021 5:21:49 PM
1235635	02/10/2021 10:05:12 AM	02/15/2021 4:45:30 PM
1235636	03/01/2021 8:30:45 AM	03/05/2021 6:10:20 PM
1235637	04/12/2021 2:20:50 PM	04/18/2021 5:55:33 PM
1235638	05/05/2021 9:15:00 AM	05/10/2021 7:45:29 PM

I have created a Calendar Table using the following DAX:

CalendarTable =

VAR MinDate =

    CALCULATE(

        MIN('SF_Case'[CreatedDate]),

        ALL('SF_Case')

    ) -- Get the earliest available date from both Createddate and Closeddate

VAR MaxDate =

    CALCULATE(

        MAX('SF_Case'[Createddate]),

        ALL('SF_Case')

    ) -- Get the latest available date from both Createddate and Closeddate

VAR BaseCalendar = CALENDAR(MinDate, MaxDate)

RETURN

ADDCOLUMNS(

    BaseCalendar,

    "Day", DAY([Date]),

    "Day Name", FORMAT([Date], "dddd"),

    "Month", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Month Year", FORMAT([Date], "MMM YYYY"),

    "Month Year ID", YEAR([Date]) * 100 + MONTH([Date]),

    "Quarter", "Q" & FORMAT([Date], "Q"),

    "Quarter Year", FORMAT([Date], "\QQ YYYY"),

    "Quarter Year Sort", YEAR([Date]) * 10 + FORMAT([Date], "Q"),

    "Start of Month", EOMONTH([Date], -1) + 1,

    "Start of Week", [Date] - WEEKDAY([Date], 2) + 1,

    "Week ID", YEAR([Date]) * 100 + WEEKNUM([Date], 2),

    "WeekDay Number", WEEKDAY([Date], 2),

    "Year", YEAR([Date]),

    "Week of Month", "Week " & ROUNDUP(DAY([Date]) / 7, 0)

)

I have taken the Month Year column from this Calendar Table as a slicer.

What I Have Tried So Far:

• To calculate the count of IDs by Year, I created a relationship between the Calendar Table’s [Date] column and the Closed Date column in my main table.
• To calculate the count of IDs by Month, I duplicated the main table and created a relationship between the Calendar Table’s [Date] column and the Created Date column in the duplicated table.
• I used the following DAX logic to count IDs by Year based on the Closed Date:

Count_Cases =

VAR selectedmonthyear= SELECTEDVALUE('CalendarTable'[Month Year])

VAR selectedyear = YEAR(DATEVALUE("01" & selectedmonthyear))

Return

CALCULATE(

    COUNTROWS('table'),

YEAR(table[ClosedDate])=selectedyear

)

Issue:

Even though I have established relationships between the tables, the visual is showing blank values instead of expected counts.

Requirements:

1. I want to calculate the count of IDs by Year (considering the Closed Date).
2. I want to calculate the count of IDs by Month (considering the Created Date).
3. Why is the visual showing blank values even though the relationships exist?
4. What is the best approach to achieve this without duplicating the table

[Punithurs]

Hi @SBC , 
Idea is to calculate no of orders in a month wrt Created date and in a year wrt closed date. 
Instead of creating a bridge table use active and inactive releationship in the modelling between calendar table and fact table. like Created date and Calendar's date as active relationship and Closed date and calendar's date as inactive. 
 and use "USERELATIONSHIP"  
MeasureClosed = 

CALCULATE(

    COUNT(FactTable[ID ]),USERELATIONSHIP('Calendar'[Date],FactTable[Closed Date]),

YEAR(Calendar[Date]) =YEAR(

SELECTEDVALUE('Calendar'[Date]))

Measure Created = 

CALCULATE(

    COUNT(FactTable[ID ]),

MONTH('Calendar'[Date])=MONTH(Calendar[Date])

)

[Poojara_D12 · Accepted solution]

Hi @SBC 

The issue you're facing, where the visual shows blank values despite having relationships between the CalendarTable and your main table, arises because Power BI can only have one active relationship between two tables. Since you're trying to calculate the count of IDs based on both ClosedDate (for the Year count) and CreatedDate (for the Month count), you need a method that allows you to work with both dates effectively without duplicating the main table.

To resolve this, you can leverage the USERELATIONSHIP function in your DAX measures. Here's how:

Count_Cases_Year = 
VAR selectedyear = YEAR(DATEVALUE("01" & SELECTEDVALUE('CalendarTable'[Month Year])))
RETURN
CALCULATE(
    COUNTROWS('table'),
    YEAR('table'[ClosedDate]) = selectedyear
)

Count_Cases_Month = 
VAR selectedmonthyear = SELECTEDVALUE('CalendarTable'[Month Year])
VAR selectedmonth = MONTH(DATEVALUE("01" & selectedmonthyear))
VAR selectedyear = YEAR(DATEVALUE("01" & selectedmonthyear))
RETURN
CALCULATE(
    COUNTROWS('table'),
    YEAR('table'[CreatedDate]) = selectedyear,
    MONTH('table'[CreatedDate]) = selectedmonth,
    USERELATIONSHIP('CalendarTable'[Date], 'table'[CreatedDate])
)

By using USERELATIONSHIP to activate the appropriate relationships for CreatedDate and ClosedDate only when needed, you avoid duplicating the table and can calculate the count of IDs both by Year (based on ClosedDate) and by Month (based on CreatedDate). This approach resolves the issue of blank values, allowing you to get accurate counts in your visual.