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!

@SergeiBaklan 

​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!@SergeiBaklan   Read More

​