Replicate quarter data to month using both active and inactive relationships

[Astrid_M-vB73]

Hi there,

As I am facing a PBI challenge, that I cannot get my head around, I am reaching out to you for help.

I have a table that is set up with data which is meant for quarterly purpose.

ReferenceDate	From	To	Value
31-03-2020	01-01-2020	31-03-2020	1,000
30-06-2020	01-04-2020	30-6-2020	1,500
30-09-2020	07-01-2020	30-09-2020	2,000
31-12-2020	10-01-2020	31-12-2020	2,500

The second table in the model is the DimDate table:

DimDate
Date	Month	Year
1/1/2020	January	2020
1/2/2020	January	2020
1/3/2020	January	2020
1/4/2020	January	2020
1/5/2020	January	2020
1/6/2020	January	2020
1/7/2020	January	2020
ETC

Active Relationship = Table.From & DimDate.Date

Inactive Relationship = Table.To & DimDate

At this moment the result looks (ofcourse!) like this:

Month	Value
March	1,000
June	1,500
September	2,000
December	2,500

However, other data on my dashboard is reported on a monthly bases and not on a quarterly. Since the Month filter is set to 'Single selection' only, I would like to replicate the (quarter-) Value for each month. Therefore the end result should look like this:

DimDate	Table1
Month	Value
January	1,000
February	1,000
March	1,000
April	1,500
May	1,500
June	1,500
July	2,000
August	2,000
September	2,000
October	2,500
November	2,500
December	2,500

So, I need to work with the active and inactive relationship, cause when I filter for example 'Februari' in the slicer (coming from DimDate) - it needs to check if the month is in the range 'From' and 'To'  and if so it needs to show the (quarter) value that applies for the selected month. In this construction I most likely need to use the DAX function - USRELATIONSHIP, but is powerBI able to put all these requirements in 1 DAX formula?

Many thanks in advance for your help.

Regards,

Astrid

[Astrid_M-vB73]

We have a winner....!

The formulas are doing what they have to do. I am happy. Thanks for your quick reply!

[amitchandak · Accepted solution]

@Astrid_M-vB73 , Measure with from date

QTD = CALCULATE(SUM(Table[Value]),DATESQTD(('Date'[Date])))

To Date

QTD TO= CALCULATE( CALCULATE(SUM(Table[Value]),USERELATIONSHIP('Table'[TO];'])),'[Date])),DATESQTD(('Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.