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 f...

admin

BI & Data Analysis

Sep 24, 2024

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.

Excellove15

Iron Contributor

Sep 25, 2024

Hi SergeiBaklan

Many thanks for your quick response sir!

Currently we are having a look into it & trying to implement in our model.

We will get back to you on this.

Meanwhile I am curious to learn how did you learn these skills for power bi. Did you read books or watch some channels to enhance your skills?

Is there any books or websites or channels that you can suggest me which i can read/learn to improve my skills in power bi, dax & modeling etc.?

Thanks in advance!

SergeiBaklan

Diamond Contributor

Sep 25, 2024

Excellove15

I'm not a pro in Power BI, mainly practice on tasks I do from time to time. Main source of information for me is sqlbi.com, I started from their books Books - SQLBI and follow articles the are publishing.

Forum Discussion

Create relationship between calendar and fact using Date range

Resources