Forum Discussion

KilianM's avatar
KilianM
Frequent Visitor
5 years ago
Solved

Dynamic price change between two Date slicers

Hello everyone ! 🙂 I'm working on a specific report where we want to compare price variation between selection from two date slicers. 🤑   The datamodel is the following :  I have my Calendar ...
Need Help
Tips and Tricks
  • KilianM's avatar
    KilianM
    5 years ago
    If someone find a better solution don't hesitate to challenge it ! 🙂

    Price Change =
    VAR E = CALCULATETABLE(SELECTCOLUMNS('PRICE',"Reference",'PRICE'[Reference],"Seller",'PRICE'[Seller],"Brand",'PRICE'[Brand]),NOT(ISBLANK('PRICE'[Price])))
    VAR F = ADDCOLUMNS(E,"ID",[Reference]&[Seller]&[Brand])
    VAR E2 = CALCULATETABLE(SELECTCOLUMNS('PRICE',"Reference2",'PRICE'[Reference],"Seller2",'PRICE'[Seller],"Brand2",'PRICE'[Brand]),ALL('CALENDAR'[WeekYear]),USERELATIONSHIP(COMPARE_DATE[WeekYear],'PRICE'[WeekYear]),NOT(ISBLANK('PRICE'[Price])))
    VAR F2 = ADDCOLUMNS(E2,"ID",[Reference2]&[Seller2]&[Brand2])
    VAR F3 = NATURALINNERJOIN(F,F2)
    VAR F4 = SELECTCOLUMNS(F3,"ID",[ID])

    RETURN
    CALCULATE('PRICE'[Price Change],'PRICE'[ID] IN F4)


    PRICE CHANGE = 
    CALCULATE(DIVIDE([Actual Price]-[Last Price],[Last Price])))
KilianM's avatar
KilianM
Frequent Visitor
5 years ago
If someone find a better solution don't hesitate to challenge it ! 🙂

Price Change =
VAR E = CALCULATETABLE(SELECTCOLUMNS('PRICE',"Reference",'PRICE'[Reference],"Seller",'PRICE'[Seller],"Brand",'PRICE'[Brand]),NOT(ISBLANK('PRICE'[Price])))
VAR F = ADDCOLUMNS(E,"ID",[Reference]&[Seller]&[Brand])
VAR E2 = CALCULATETABLE(SELECTCOLUMNS('PRICE',"Reference2",'PRICE'[Reference],"Seller2",'PRICE'[Seller],"Brand2",'PRICE'[Brand]),ALL('CALENDAR'[WeekYear]),USERELATIONSHIP(COMPARE_DATE[WeekYear],'PRICE'[WeekYear]),NOT(ISBLANK('PRICE'[Price])))
VAR F2 = ADDCOLUMNS(E2,"ID",[Reference2]&[Seller2]&[Brand2])
VAR F3 = NATURALINNERJOIN(F,F2)
VAR F4 = SELECTCOLUMNS(F3,"ID",[ID])

RETURN
CALCULATE('PRICE'[Price Change],'PRICE'[ID] IN F4)


PRICE CHANGE = 
CALCULATE(DIVIDE([Actual Price]-[Last Price],[Last Price])))