CODEWITHSUNDEEP
powerbidax
tejeswar reddy
tejeswar reddy

Reputation: 1

how to get the price of the last 3 days in Power BI Dax

Aswer should be like thisHi I want to calculate the price of the vehicle 3day before for every day extract into a new column

I used the below formula and I am not getting correct value

3dayPrice = CALCULATE(AVERAGE(PowerBI_HistoricalMarketPricing[DailyRate]),FILTER(PowerBI_HistoricalMarketPricing,DATEADD(PowerBI_HistoricalMarketPricing[Extract Date],-3,DAY)))

enter image description here Hi I need the values like below For selected Pickup date and selected car, the values should show the extract date-3days price for every extract just like in the below sample Car enter image description here

Upvotes: 0

Views: 712

Answers (1)

msta42a
msta42a

Reputation: 3741

You need to use ALL inside FILTER to remove context filter (row context)

Price3dayAgo = CALCULATE(sum(PowerBI_HistoricalMarketPricing[DailyRate]),FILTER(ALL(PowerBI_HistoricalMarketPricing) PowerBI_HistoricalMarketPricing[Extract Date] = SELECTEDVALUE(PowerBI_HistoricalMarketPricing[Extract Date]) -3 ))

AVGofLast3Days = CALCULATE(AVG(PowerBI_HistoricalMarketPricing[DailyRate]),FILTER(ALL(PowerBI_HistoricalMarketPricing) PowerBI_HistoricalMarketPricing[Extract Date] >= SELECTEDVALUE(PowerBI_HistoricalMarketPricing[Extract Date]) -3 && PowerBI_HistoricalMarketPricing[Extract Date] < SELECTEDVALUE(PowerBI_HistoricalMarketPricing[Extract Date])))

If we need to check additional condition for example Car

&& SELECTEDVALUE(PowerBI_HistoricalMarketPricing[Car]) = PowerBI_HistoricalMarketPricing[Car]

Upvotes: 1

Related Questions