Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
1 year ago

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!

marcorusso Ahmedx Anonymous jgeddes Anonymous Greg_Deckler 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

9 Replies

    • sivarajan21's avatar
      sivarajan21
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      Hi marcorusso ,

       

      Thanks for your quick response!

      Apologise for the delay as I was reading through your article on events in progress. Thats really a well crafted article and loved it!

       

      I am trying to understand and implement the same in my report but i am struggling with it.

      I want to recall/give more info on this query.  Below is my sample model

       

      The relationship has to flow from calendar to fact table(Electricity Summary) 'one to many' in order to find out the average cost(cost column in fact table) per month(Also at daily level). This means our visual would look something like below:

      The month is coming from calendar table for above visual and values from dax measure that we created.

      What I am confused here is calendar table has daily granularity and fact table has two date columns(Date & Date previous),so how to calculate average cost on a day level in fact table?

       

      One idea that we had is as below:

      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.

       

      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

      For example, in screenshot above for rows 3 & 4 of fact table.

      So the key thing is finding the number of days. Use that(no of days) to divide cost.

       

      I am not sure whether above approach is correct or not? could you please guide us?

      Below is my original model of report:

       

      I read through the snapshot in that article but confused how that can be related to my query.

      If its possible, could you please give me a heads up on how to create dax measure for this model?

      Also, I don't know how we apportion the data.

      Please let me know if you need further info!

       

      Thanks in advance!

      marcorusso @Ahmedx @v-linyulu-msft @jgeddes @v-yaningy-msft @Greg_Deckler 

       

      • Jpss's avatar
        Jpss
        Icon for Resolver II rankResolver II
        1 year ago

        Hey sivarajan21 ,
        I have created following solution for you mostly in power query. 

         

        I have expanded the table to include all dates between start date and end date. and also daily amount has been calculated by dividing the original number by the number of days.



         

        this gives you daily granularity. 

        since you mentioned that you have millions of rows, this could be an expensive operation performance wise. so best alternative would be to push this transformation back to sql.  

         

        however if you choose to go this way, you can then create further summary tables from this base table, if you need to only see higher level data 

         

        here is the pbix for your reference sivarajan21 _4167328_JPSS_SOL.pbix

        If you find this helpful, Consider marking this as a solution