Forum Discussion

HarsimranPWRBI's avatar
HarsimranPWRBI
Helper I
7 years ago
Solved

Problem with USERELATIONSHIP

I'm trying to create a measure to get Sales Amount w.r.t Delivered Date where CalenderYear is 2007 but having trouble with same.   For Sales Amount w.r.t Delivered Date works well  and show amount ...
Help
  • avanderschilden's avatar
    avanderschilden
    7 years ago

    Hello,

     

    Try this (with the correct table and column names from your model) ;

    Delivered Amount 2007 =
    CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP('Date'[Date],Sales[DeliveryDate]),
    YEAR(Sales[DeliveryDate])=2007
    )
     
    Let me know!
     
    Regards,
     
    Adrian
avanderschilden's avatar
avanderschilden
Resolver I
7 years ago

This will show the delivered amount in 2017 on all year lines;

 

Delivered Amount 2017 =
CALCULATE([Delivered Amount],
YEAR('Date'[Date])=2018)
HarsimranPWRBI's avatar
HarsimranPWRBI
Helper I
7 years ago

yeah avanderschilden  changing your first expression from 

CALCULATE(
[SalesAmount],// its a measure with expression SUMX(Sales,Sales[Quantity]*Sales[Unit Price])
USERELATIONSHIP('Date'[DateKey],Sales[DeliveryDateKey]),
YEAR('Date'[Date])=2007 // Changed from YEAR(Sales[DeliveryDate])=2007 allowed 2007 sales w.r.t to delivered date on all CY
)

Thanks for your guidance, much appreciated :manhappy:

 

Regards,

Harry