between date slicer to get two turnovers based on price start end vs price en date

[Anonymous]

Dear all,

I read a lot of posts but I couldn't find any similar situation to help me.

I created a dummy dataset in order to explain my expected result.

I got thoses tables which should be self-explanatory 

And here is the expected result with explanation

- start_price : looks for the price of the id at the start date (red) within the PRICE TABLE + total (which is the average of the above column)

- start_price : looks for the price of the id at the end date (green) within the PRICE TABLE + total (which is the average of the above

- quantity_sold : quantity sold for that period within SALES TABLE + total (which is the sum of the above column)

- turnover_start_date_price : is the quantity_sold multiply by the start_price + total (which is the sum of the above column)

- turnover_end_date_price : is the quantity_sold multiply by the end_price+ total (which is the sum of the above column) 

I was able to get every data right EXCEPT the total which is not the right amount !

You can download this dummy file here : https://docs.google.com/spreadsheets/d/1bJ6QMG2ui1iy9OHSU2A9NDfe7d6ajL2-/edit?usp=sharing&ouid=10108...

Thank you in advance for your precious help

[Anonymous · Accepted solution]

Weirdly, I was not able to reproduce it with my real pbix. But thanks to your attempt, I could find a working way to get the right measures.

I actually missed VALUES('PRODUCT TABLE'[id]) from purchase_price_start_date which seemed to be the reason why my total was wrong.

- start_price = CALCULATE (AVERAGE('PRICE TABLE'[price]),'PRICE TABLE[price_date]=MIN('CALENDAR TABLE'[date]))
- end_price =CALCULATE (AVERAGE('PRICE TABLE'[price]),'PRICE TABLE[price_date]=MAX('CALENDAR TABLE'[date]))
- quantity_sold = CALCULATE(SUM(SALES TABLE[sales_quantity]))

- purchase_turnover_start_date =

    SUMX(VALUES('PRODUCT TABLE'[id]),

        [start_price ] * [quantity_sold]

    )

- purchase_turnover_start_date =

    SUMX(VALUES('PRODUCT TABLE'[id]),

        [end_price ] * [quantity_sold]

    )

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yadongf-msft]

Hi @Anonymous ,

I think this is result you want:

Please try following DAX:

Start price = IF(
    ISINSCOPE('Price table'[id]),
    CALCULATE(MIN('Price table'[price]),FILTER(ALLSELECTED('Price table'[price_date]),'Price table'[price_date] = MIN('Price table'[price_date]))),
    CALCULATE(AVERAGE('Price table'[price]),FILTER(ALLSELECTED('Price table'[price_date]),'Price table'[price_date] = MIN('Price table'[price_date])))
    )

End price = IF(
    ISINSCOPE('Price table'[id]), 
    CALCULATE(MAX('Price table'[price]),FILTER(ALLSELECTED('Price table'[price_date]),'Price table'[price_date] = MAX('Price table'[price_date]))),
    CALCULATE(AVERAGE('Price table'[price]),FILTER(ALLSELECTED('Price table'[price_date]),'Price table'[price_date] = MAX('Price table'[price_date])))
    )

Quantity_sold = CALCULATE(SUM('Sales table'[sales_quantity]),FILTER('Sales table','Sales table'[sales_date] IN VALUES('Price table'[price_date])))

Quantity_sold2 = SUM('Sales table'[sales_quantity]) - CALCULATE(SUM('Sales table'[sales_quantity]),FILTER('Sales table','Sales table'[sales_date] = MAX('Sales table'[sales_date])))

turnover_start_date_price = IF(
    ISINSCOPE('Price table'[id]),
    [Start price]*[Quantity_sold],
    SUMX(FILTER('Price table','Price table'[price_date] = MIN('Price table'[price_date])),[Start price]*[Quantity_sold2])
    )

turnover_end_date_price = IF(
    ISINSCOPE('Price table'[id]),
    [End price]*[Quantity_sold],
    SUMX(FILTER('Price table','Price table'[price_date] = DATE(2022,1,3)),[Start price]*[Quantity_sold2])
    )

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft]

Hi @Anonymous ,

The relationship between two tables:

Please try following DAX:

Start price = CALCULATE(MIN('Price table'[price]),FILTER(ALLSELECTED('Price table'[price_date]),'Price table'[price_date] = MIN('Price table'[price_date])))

End price = CALCULATE(MAX('Price table'[price]),FILTER(ALLSELECTED('Price table'[price_date]),'Price table'[price_date] = MAX('Price table'[price_date])))

Quantity_sold = CALCULATE(SUM('Sales table'[sales_quantity]),FILTER('Sales table','Sales table'[sales_date] IN VALUES('Price table'[price_date])))

turnover_start_date_price = [Start price]*[Quantity_sold]

turnover_end_date_price = [End price]*[Quantity_sold]

The result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yadongf-msft,

Thanks for your quick answer.

Unfortuanly the total is not right 😞

You got that result

But we should see those numbers 🙂

Do you have an alternative solution? Does someone else?

Thanks again