Create relationship between calendar and fact using Date range

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!

Hi SergeiBaklan 

Thanks for your prompt response sir!

This number of dates measure works like a gem after testing!

Elegance with which you wrote works well in terms of performance as well!

We have replaced the source table with our TargetTimeSeries table to calculate number of days between date range(StartDate & EndDate):

Number of Dates = 
VAR minDate = MIN( 'Calendar'[Date] )
VAR maxDate = MAX( 'Calendar'[Date] )
RETURN
    SUMX(
        TargetTimeSeries,
        IF(
            TargetTimeSeries[EndDate] < minDate || TargetTimeSeries[StartDate] > maxDate,
            BLANK( ),
            INT( MIN( TargetTimeSeries[EndDate], maxDate ) - MAX( TargetTimeSeries[StartDate], minDate ) ) + 1
        )
    )
    

We have usage column in our TargetTimeseries table. Now we have to create dax for Target consumption per day.

We need to divide this usage column with above Number of dates measure to get the daily consumption. so we created a dax measure as below:

Target consumption per day = 
VAR minDate = MIN( 'Calendar'[Date] )
VAR maxDate = MAX( 'Calendar'[Date] )
RETURN
    SUMX(
        TargetTimeSeries,
        DIVIDE(TargetTimeSeries[Usage],IF(
            TargetTimeSeries[EndDate] < minDate || TargetTimeSeries[StartDate] > maxDate,
            BLANK( ),
            INT( MIN( TargetTimeSeries[EndDate], maxDate ) - MAX( TargetTimeSeries[StartDate], minDate ) ) + 1
        )
    )
    )

Below is the output in visual:

we did a data analysis with data in model to find whether values returned by measure in visual is accurate.

So we took NetworkRail-3429. For below filter(1st April 2023) on StartDate,EndDate will show 31s March 2024 for this start date,  usage column should add up to give  12519260. 

Then according to above 'Target consumption per day' dax, dividing this 12519260 with Number of days between startdate & EndDate which is 365. The result should be 12519260/365 = 34299.

This 34299 should be populated for all months of table visual as below:

But we are not where & how below values are calculated in table visual:

We suspect because there is no relationship between Enddate(TargetTimeSeries) and Date(Calendar) & only startdate-Date relation is active this issue occurs. But not sure how to resolve this issue.

 
The current relationship between these two tables is as below:

Could please advise me on this?

Please let me know if you need further info!

PFA file here TimeSeries Test.pbix

Also, FYR PFA file of filtered Network Rail data NetworkRail3429 data.xlsx

Many thanks in advance!

SergeiBaklan 

Hi SergeiBaklan ,

I have tried to find the below solution for my question sir!  

Target consumption per day = 
VAR minDate = MIN ( 'Calendar'[Date] )
VAR maxDate = MAX ( 'Calendar'[Date] )
RETURN  CALCULATE (
    SUMX ( 
        TargetTimeSeries,
        VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate]
        VAR Result = TargetTimeSeries[Usage] / _Days
        RETURN Result
    ),
    REMOVEFILTERS ( 'Calendar' ),
    KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ),
    KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate )
)

 I was able to calculate the target consumption on a daily basis. But I am struggling to find the Target consumption per month. 

What I mean is, from above measure we are able to calculate 'target consumption per day' which is great!.

with this daily value, we now need to use this daily value to multiply by the number of days in the month of the visual to apportion this consumption correctly.

for example,

ie. if the target table started on 17/04/2023 and ended on 31/03/2024 then the daily value would be multiplied by 14. because there are 14 days in April that this relates to. green color indicates what we already achieved.

Yellow color(Target consumption per month) is what we need to find. FYR, PFA below excel file that contains few formulas for better understanding NetworkRail3429 data.xlsx

Target consumption per month should sum up this 14 days values and display for its respective month

PFA file TimeSeries Test.pbix

Please advise!

Thanks in advance!
SergeiBaklan