Forum Discussion
Create relationship between calendar and fact using Date range
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.
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!
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.
Hi SergeiBaklan ,
Many thanks sir!
we have resolved this issue!
I have marked your answer as best response!
Many thanks
