measures aggregated on different dimensions

[lolojee]

 Hi all,

I have a table with datein, dateiout two dimensions. I want to create measure1 aggregated on quantity by datein  and measure2 aggregated on price by dateout like the following table.  Any idea for this requirement? Thanks in advanced.

 

[Greg_Deckler]

Assuming that you have a disconnected Calendar table and that you use that Calender table for your year/month column in your visual:

measure1 = SUMX(FILTER(ALL('2Dimensions'),MONTH(MAX('Calendar'[Date]))=MONTH([datein])),'2Dimensions'[quantity])

measure2 = SUMX(FILTER(ALL('2Dimensions'),MONTH(MAX('Calendar'[Date]))=MONTH([dateout])),'2Dimensions'[price])

[OwenAuger · Accepted solution]

Hi @lolojee

You can do this using a calendar table which has an active relationship with one of your date columns and an inactive relationship with the other one.

Here is an example pbix.

You should set up your data model like this:

And write measures like this:

measure1 = 
SUM ( Sales[quantity] )

measure2 = 
CALCULATE (
    SUM ( Sales[price] ),
    USERELATIONSHIP ( Sales[dateout], 'Calendar'[Date] )
)

In this example, the inactive relationship is with the dateout column, so measure2 uses USERELATIONSHIP to active the inactive relationship.

Regards,

Owen