How to use calendar dates that are not part of the fact table or date table?

[bryn987]

My title is confusing but I'm strugging with this type of scenario.

I have a customer service table that logs cases and when the case was created and when it was closed etc.  I want to see a chart that trends how many cases are open on a daily basis.  I do have a date table but I have that table related to my fact table by Created Date.  In the above scenario, I do not want to use that date table as the Created Date relationship will skew my numbers. 

So, how do I use dates for this scenario?

[v-stephen-msft]

Hi @bryn987 ,

You may try creating another date table which is disconnected to your fact table by dax in Power BI Desktop or by M in Power Query. Please refer to

CALENDAR function (DAX) - DAX | Microsoft Learn

Creating Calendar Table in Power BI using DAX Functions - RADACAD

Create Calendar Table Using Power Query M Language (mssqltips.com)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[bryn987]

That was my first thought but I'm getting errors saying they need to be related.  

[v-stephen-msft]

Hi @bryn987 ,

You need to create a measure to filter rows in the main table, which can be filtered based on the disconnected calendar dates, thus creating a connection.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Greg_Deckler]

@bryn987 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[JirkaZ]

You'll have to create multiple relationships between your fact table (one based on case open date, one based on case close date).

The measures will then look something like this:

Open Cases := CALCULATE(
   COUNTROWS('Cases'), 
   USERELATIONSHIP('Cases'[DateOpen], 'DimDate'[Date])
)

You don't have to define the USERELATIONSHIP for the active relationship. So if the active relationship uses Case Close Date, then the closed cases measure doesn't have to have that USERELATIONSHIP clause.