Dynamic price change between two Date slicers

[KilianM]

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 table, and a second one with a Inactive relationship (used for the Last_Price measure).

The data samples from the tables are :

PRICE Table :

CALENDAR and COMPARE_DATE table :

The report I want to build is similar as this one. We select the two weeks to compare and I want to find the %PriceChage for the TOTO Brand (but the actual result is not the one i need) :

 

The problem is simple, I would like to have the average on the data that can be compared.

Instead of having the variation on the average of "Actual Price" and "Last Price" for all the references, I need to have the average for the A reference only. It is the only reference where we can find an actual price and a last price value.

So the number I want to have as the %Price Change for TOTO is ((6+10)/2 - ((5+5)/2) / ((5+5)/2) = 60%

The measures used are : 

Actual Price =

CALCULATE (

AVERAGE(PRICE[Price])

)

Last Price =

CALCULATE (

[Actual Price],

ALL ( 'CALENDAR'[WeekYear] ),

USERELATIONSHIP ( COMPARE_DATE[WeekYear], 'PRICE'[WeekYear] )

)

Price Change = IF(NOT ISBLANK([Actual Price]),CALCULATE(DIVIDE([Actual Price]-[Last Price],[Last Price])))

PS : I found this ticket but i didn't understand the answer : https://community.powerbi.com/t5/Desktop/Calculated-Column-Table-Change-Dynamically-According-to-Sli...

If you find answer to my problem I will be very grateful to you. Thanking you. 🤗 

I am not even sure that it is necessary to have a second calendar table.

BR,

Kilian

[KilianM · Accepted solution]

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

[amitchandak]

@KilianM , refer to my blog or video on how to deal with two slicers

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

or

https://www.youtube.com/watch?v=WSeZr_-MiTg

[KilianM]

Hello @amitchandak,

Thank you for your answer.
My problem is how to compare only the references with prices in the two contexts.

Do you know how to deal with this problematic ?

Thank you,

Best regards,

KM