Combining measures with diffent calendars

[Anonymous]

Hello,

I have a table with different dates. The table includes columns for print date, close date and modified date . To generate reports with related values with these dates, I have created a Print date Calendar, close date calendar and a modified date calendar. In this way I am able to calculate for example the measure Sales per print date. 

As each calendar is linked to a certain measure I am not able to combine these measure using one calendar.

Print date are actual dates whereas close dates are dates in the future.

My goal is to have one table with one calendar in in which all measures are shown.

Thanks

[RobbeVL]

Hi there,

This is possible using 1 Date Dimension.
You can create the extra relationships in your model, they will automatically become NON-Active relationships.
Not active doesnt mean you cannot activate them in a measure.

ex. In a Calculate measure you can do the following:
CALCULATE( SUM(SALES); printdate = "2018"; USERELATIONSHIP(DATEKEY,PRINTDATE))

This will use the required relationship.

I hope this helps!

Robbe

[Anonymous · Accepted solution]

Hi Robbe,

I came also across this solution https://carldesouza.com/power-bi-dax-userelationship-multiple-dates/ Is this what you mean?

So creating new measures using a non active relationship?

[Cmcmahan]

That's exactly the solution the both of us suggested, yes. 

If you have further problems implementing it, feel free to follow up here.

[Cmcmahan]

So the first step would be to just create one date dimension table and relate it to all 3 columns.  Set whichever one you would use by default most often as the 'active' relationship, the other two will be inactive.

Then in your measures, you can use USERELATIONSHIP to switch the active relationship in a measure.  For example, if you defaulted to Print Date, but wanted to sum groups based on the Close Date, you could write an expression like this one.  You can read more about it here. 

SumClosed = CALCULATE( SUM(Table1[SummableValue]), USERELATIONSHIP( Table1[CloseDate], Calendar[DateKey]))

There's also the option of using TREATAS instead, which can be useful for more complicated relationships.  Further reading can be found here.