Forum Discussion

lolojee's avatar
lolojee
New Member
8 years ago
Solved

measures aggregated on different dimensions

 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.  An...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

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])