Create relationship between calendar and fact using Date range

Hi Team,

I have two tables as below:

The fact table has date range in a imbalanced way as below:

For example, row 1 and 2 contains full days in a month(April & May).

But for row 3 contains only 1st 20 days and row 4 contains next 10 days.

This is how we get the data and we can do nothing about it.

We want to create a relationship from this fact table to calendar table in order to find the average cost per month.

If we find the average as it is from above fact table, it gives average as 200 which is wrong :

Correct one is if we add the 3rd and 4th row values because it counts to full april month(01/04/2023 - 30/04/2023) then it counts to 500. average is 266.67. But data doesn't come as we wish.

As the fact table contains dates in two columns(date range), we first convert this 2 column of dates into a single list of date column in power query(dataflow). Then connect that date column to calendar date and find the average cost per month.

Now what happens is that, as our original data(Fact) contains millions of rows, this operation of converting two column of dates into a single column(each day) causes lot of performance issues. This causes data flow refresh issues, report to slow down etc.

Is there any other ways we can create relationship between calendar table and fact table, without converting range of dates into day granularity in fact table but still pick up the cost for those dates?

Is it possible to create a dax measure that create a relationship between these two tables ?

Please suggest.

FYR, PFA sample file here Test.pbix

Thanks in advance!

For such table

measure for number of days could be

Number of Dates = 
VAR minDate = MIN( 'Date'[Date] )
VAR maxDate = MAX( 'Date'[Date] )
RETURN
    SUMX(
        Source,
        IF(
            Source[Date To] < minDate || Source[Date From] > maxDate,
            BLANK( ),
            INT( MIN( Source[Date To], maxDate ) - MAX( Source[Date From], minDate ) ) + 1
        )
    )

which gives

No relationships between Date and Source

Didn't catch how you calculate average cost, so ignored that.

Lorenzo
Silver Contributor
Sep 24, 2024
Excellove15

Are the below AVGs what you're looking for?
- Excellove15
  Iron Contributor
  Sep 24, 2024
  Hi Lorenzo
  
  Thanks for your quick response!
  May i know how that average was created? Can i see the dax measure please?
  
  Just FYI, Its not only about the average but find out the number of days between 'date from' and 'date to'(in fact table) using a dax measure and divide that by cost to find average.
  For example, in screenshot below for rows 3 & 4:
  
  We need a dax to make sure that it calculates number of days between 'date from' and 'date to' of the particular month in the visual.
  We need to account for 2 scenarios where:
  1)The start and end date are within the particular month or
  2)More importantly where the end date is outside of the month in question
  
  So the key thing is finding the number of days. Use that(no of days) to divide cost.
  My expected output visual would be something like this:
  
  Please let me know if you need further info!
  
  Thanks in advance!
  SergeiBaklan Lorenzo
  - SergeiBaklan
    MVP
    Sep 24, 2024
    Excellove15
    
    For such table
    
    measure for number of days could be
    
    Number of Dates = VAR minDate = MIN( 'Date'[Date] ) VAR maxDate = MAX( 'Date'[Date] ) RETURN SUMX( Source, IF( Source[Date To] < minDate || Source[Date From] > maxDate, BLANK( ), INT( MIN( Source[Date To], maxDate ) - MAX( Source[Date From], minDate ) ) + 1 ) )
    
    which gives
    
    or
    
    No relationships between Date and Source
    
    Didn't catch how you calculate average cost, so ignored that.

Forum Discussion

Create relationship between calendar and fact using Date range

Share

Resources