Forum Discussion
Calculate Minimum Over Group and Get Associated Value For Calculation
I have a data set with car values (example data) where I want to calculate "Residual Pct" from the user's selected date go forward. This all starts with a drop down slicer for the user to select the starting "Qtr Date". Based on this selection, I need to capture the "Value" associated with that date. THEN, using that captured "Value" we calculate a "Residual Pct" for each quarter after and including the user's selected start date. This is done at the Make/Model level as well so it resets the initial value at the Make/Model level.
In the example, the data is by quarter by Make/Model for 2 years. The user in the slicer drop down selects "4/1/2023". Given this selection, I need to capture "$19,000" for the Toyotal Camry and "40,000" for the Nissan Maxima. Finally, the "Residual Pct" is calculated for EACH quarter respective to each Make/Model but only starting with what the user selected as the "Qtr Date" in the slicer.
Below is an example. I've tried numerous ways with examples but still can't resolve. Appreciate any help.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
3 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Below is sample data. Expected results are in the yellow column, Residual Pcts. For some reason with the data below I get an error message about HTML issue and it is pasting that in a weird format that doesn't look to be copy/paste well.
Make Model Qtr Date Value Toyota Camry 1/1/2023 20000 Toyota Camry 4/1/2023 19000 Toyota Camry 7/1/2023 19500 Toyota Camry 10/1/2023 18000 Toyota Camry 1/1/2024 17000 Toyota Camry 4/1/2024 17000 Toyota Camry 7/1/2024 16500 Toyota Camry 10/1/2024 17500 Nissan Maxima 1/1/2023 40000 Nissan Maxima 4/1/2023 40000 Nissan Maxima 7/1/2023 38000 Nissan Maxima 10/1/2023 36000 Nissan Maxima 1/1/2024 37000 Nissan Maxima 4/1/2024 38000 Nissan Maxima 7/1/2024 34000 Nissan Maxima 10/1/2024 34000 - lbendlin
