Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Good Morning;
I am trying to create a report that will look at the vin# and sale date in one table an match the VIN # in another table and look at the RO dates for that vin. if the date on the ro is with 8 months of the sale date return a value of "1" of not then "0". I have attached a sample of the data i am using. any help would be greatly appreciated.
Vehicle Sales
Dealer Coder | Dealer Name | Vehicle Identification Number (VIN) | Model Year | Sale Date | Units Sold |
123456 | 123456 Toyota | 1123 | 2022 | 05/2022 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | 05/2022 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | 07/2022 | 1 |
123465 | 123465 Toyota | 1138 | 2023 | 04/2023 | 1 |
123468 | 123468 Toyota | 1143 | 2022 | 09/2022 | 1 |
123471 | 123471 Toyota | 1148 | 2022 | 06/2022 | 1 |
123474 | 123474 Toyota | 1153 | 2022 | 06/2022 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | 05/2022 | 1 |
123480 | 123480 Toyota | 1163 | 2022 | 06/2022 | 1 |
123483 | 123483 Toyota | 1168 | 2022 | 08/2022 | 1 |
123486 | 123486 Toyota | 1173 | 2022 | 11/2022 | 1 |
123489 | 123489 Toyota | 1178 | 2022 | 05/2022 | 1 |
123492 | 123492 Toyota | 1183 | 2022 | 06/2022 | 1 |
123495 | 123495 Toyota | 1188 | 2022 | 06/2022 | 1 |
123498 | 123498 Toyota | 1193 | 2022 | 07/2022 | 1 |
Service History
Dealer Code | Dealer Name | Service VIN | Model Year | Service RO Number | RO Date | Total RO's |
123456 | 123456 Toyota | 1123 | 2022 | SA26013 | 03/2022 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | ST83176 | 03/2020 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | ST85628 | 05/2020 | 1 |
123465 | 123465 Toyota | 1138 | 2022 | SA00628 | 12/2020 | 1 |
123468 | 123468 Toyota | 1143 | 2022 | SA08750 | 05/2021 | 1 |
123471 | 123471 Toyota | 1148 | 2022 | SA20569 | 12/2021 | 1 |
123474 | 123474 Toyota | 1153 | 2022 | SA31535 | 06/2022 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | SA42994 | 01/2023 | 1 |
123480 | 123480 Toyota | 1163 | 2022 | ST79067 | 01/2020 | 1 |
123483 | 123483 Toyota | 1168 | 2022 | ST93092 | 08/2020 | 1 |
123486 | 123486 Toyota | 1173 | 2022 | SA20559 | 12/2021 | 1 |
123489 | 123489 Toyota | 1178 | 2022 | SA40133 | 11/2022 | 1 |
123492 | 123492 Toyota | 1183 | 2022 | SA05676 | 04/2021 | 1 |
123495 | 123495 Toyota | 1188 | 2022 | SA21173 | 12/2021 | 1 |
123498 | 123498 Toyota | 1193 | 2022 | SA26829 | 04/2022 | 1 |
123456 | 123456 Toyota | 1123 | 2022 | ST99577 | 12/2020 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | SA06770 | 04/2021 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | SA18667 | 11/2021 | 1 |
123465 | 123465 Toyota | 1138 | 2022 | SA25137 | 03/2022 | 1 |
123468 | 123468 Toyota | 1143 | 2022 | SA28709 | 05/2022 | 1 |
123471 | 123471 Toyota | 1148 | 2022 | SA35554 | 09/2022 | 1 |
123474 | 123474 Toyota | 1153 | 2022 | SA38248 | 10/2022 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | ST96375 | 10/2020 | 1 |
123480 | 123480 Toyota | 1163 | 2022 | SA02544 | 02/2021 | 1 |
Solved! Go to Solution.
AB,
Dumb question: Do you have a relationship created in Power BI between the two tables like this? I assumed you had it in your data model, because otherwise, you're correct. My calculated column measure would not be taking the VIN into account.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Proud to be a Super User! | |
IHello Wilson, My apologies yes it would be after the sale date within the 8 months. as far as output if i could have it as a column on the vehicles sales table that would be great. as i could then calculate the percentage of vehicle sales and how many return for service within 8 months. kind of like like the below table. Thank you again for any help. much appreciated
Dealer Coder | Dealer Name | Vehicle Identification Number (VIN) | Model Year | Sale Date | Units Sold | Serviced with 8 mths |
123456 | 123456 Toyota | 1123 | 2022 | 05/2022 | 1 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | 05/2022 | 1 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | 07/2022 | 1 | 1 |
123465 | 123465 Toyota | 1138 | 2023 | 04/2023 | 1 | 0 |
123468 | 123468 Toyota | 1143 | 2022 | 09/2022 | 1 | 0 |
123471 | 123471 Toyota | 1148 | 2022 | 06/2022 | 1 | 0 |
123474 | 123474 Toyota | 1153 | 2022 | 06/2022 | 1 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | 05/2022 | 1 | 0 |
123480 | 123480 Toyota | 1163 | 2022 | 06/2022 | 1 | 1 |
123483 | 123483 Toyota | 1168 | 2022 | 08/2022 | 1 | 1 |
123486 | 123486 Toyota | 1173 | 2022 | 11/2022 | 1 | 1 |
123489 | 123489 Toyota | 1178 | 2022 | 05/2022 | 1 | 1 |
123492 | 123492 Toyota | 1183 | 2022 | 06/2022 | 1 | 1 |
123495 | 123495 Toyota | 1188 | 2022 | 06/2022 | 1 | 1 |
123498 | 123498 Toyota | 1193 | 2022 | 07/2022 | 1 | 1 |
Hello AB2,
Try this as a column in your Vehicle Sales table.
Service Within 8 Months of Sale =
VAR DateSold = 'Vehicle Sales'[Sale Date]
VAR SrvHistFiltered = -- Filtering Service History table for only RO dates after the sale date; unclear why there are RO dates before sale
FILTER (
'Service History',
'Service History'[RO Date] >= DateSold
)
VAR FirstServiceDate = MINX ( SrvHistFiltered, 'Service History'[RO Date] )
VAR EightMonthsAfterSale = EDATE ( DateSold, 8 )
RETURN
IF ( FirstServiceDate > DateSold && FirstServiceDate <= EightMonthsAfterSale, 1, 0 )
However, I'm not getting the same results in the column as you showed here. For example, for VIN 1123, I'm only seeing service on 12/1/2020 and 3/1/2022, which are both before the sale date of 5/1/2022 (and therefore not within the 8 months following). Therefore, I'm returning a 0 on that vehicle. If that's incorrect, please let me know where I'm misunderstanding your requirements.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Proud to be a Super User! | |
Hi Wilson;
I have tried that and it returns the 0 value. The dates on the sample are my mistake in replicating the data. i made an error on the dates. I have edited the dates but it returns 1 even when the date is over 8 months ? Could it bee that it is not Matching the vin#'s on the vehicle sales table and the Service history table? here are the edited tables
Service History
Dealer Code | Dealer Name | Service VIN | Model Year | Service RO Number | RO Date | Total RO's |
123456 | 123456 Toyota | 1123 | 2022 | SA26013 | 01/01/23 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | ST83176 | 02/01/23 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | ST85628 | 03/01/23 | 1 |
123465 | 123465 Toyota | 1138 | 2022 | SA00628 | 06/01/23 | 1 |
123468 | 123468 Toyota | 1143 | 2022 | SA08750 | 05/01/23 | 1 |
123471 | 123471 Toyota | 1148 | 2022 | SA20569 | 05/01/23 | 1 |
123474 | 123474 Toyota | 1153 | 2022 | SA31535 | 01/01/23 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | SA42994 | 01/01/23 | 1 |
123480 | 123480 Toyota | 1163 | 2022 | ST79067 | 02/01/23 | 1 |
123483 | 123483 Toyota | 1168 | 2022 | ST93092 | 01/01/23 | 1 |
123486 | 123486 Toyota | 1173 | 2022 | SA20559 | 12/01/23 | 1 |
123489 | 123489 Toyota | 1178 | 2022 | SA40133 | 03/01/23 | 1 |
123492 | 123492 Toyota | 1183 | 2022 | SA05676 | 01/01/23 | 1 |
123495 | 123495 Toyota | 1188 | 2022 | SA21173 | 01/01/23 | 1 |
123498 | 123498 Toyota | 1193 | 2022 | SA26829 | 02/01/23 | 1 |
123456 | 123456 Toyota | 1123 | 2022 | ST99577 | 01/01/23 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | SA06770 | 02/01/23 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | SA18667 | 06/01/23 | 1 |
123465 | 123465 Toyota | 1138 | 2022 | SA25137 | 06/01/23 | 1 |
123468 | 123468 Toyota | 1143 | 2022 | SA28709 | 05/01/23 | 1 |
123471 | 123471 Toyota | 1148 | 2022 | SA35554 | 06/01/23 | 1 |
123474 | 123474 Toyota | 1153 | 2022 | SA38248 | 01/01/23 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | ST96375 | 01/01/23 | 1 |
123480 | 123480 Toyota | 1163 | 2022 | SA02544 | 02/01/23 | 1 |
Vehicles Sales
Dealer Coder | Dealer Name | Vehicle Identification Number (VIN) | Model Year | Sale Date | Units Sold |
123456 | 123456 Toyota | 1123 | 2022 | 05/2022 | 1 |
123459 | 123459 Toyota | 1128 | 2022 | 05/2022 | 1 |
123462 | 123462 Toyota | 1133 | 2022 | 07/2022 | 1 |
123465 | 123465 Toyota | 1138 | 2023 | 04/2023 | 1 |
123468 | 123468 Toyota | 1143 | 2022 | 09/2022 | 1 |
123471 | 123471 Toyota | 1148 | 2022 | 06/2022 | 1 |
123474 | 123474 Toyota | 1153 | 2022 | 06/2022 | 1 |
123477 | 123477 Toyota | 1158 | 2022 | 05/2022 | 1 |
123480 | 123480 Toyota | 1163 | 2022 | 06/2022 | 1 |
123483 | 123483 Toyota | 1168 | 2022 | 08/2022 | 1 |
123486 | 123486 Toyota | 1173 | 2022 | 11/2022 | 1 |
123489 | 123489 Toyota | 1178 | 2022 | 05/2022 | 1 |
123492 | 123492 Toyota | 1183 | 2022 | 06/2022 | 1 |
123495 | 123495 Toyota | 1188 | 2022 | 06/2022 | 1 |
123498 | 123498 Toyota | 1193 | 2022 | 07/2022 | 1 |
AB,
Dumb question: Do you have a relationship created in Power BI between the two tables like this? I assumed you had it in your data model, because otherwise, you're correct. My calculated column measure would not be taking the VIN into account.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Proud to be a Super User! | |
Good Morning Wilson;
I had the relationship but i did not have it correctly set up. it works now. thank you very much for your assistance. much appreciated
You're welcome, glad you got it working. 😄
Proud to be a Super User! | |
Hello AB2,
Thanks for making your data easy to replicate. 😄
When you say "the RO is within 8 months of the sale date", do you mean on either side of the sale date (ie: within eight months before or after)? I started looking at this assuming RO dates would only be after sale date but that doesn't seem to be the case.
Also, do you also have a sample report output/format you're looking for? That part was a little ambiguous to me.
Proud to be a Super User! | |
User | Count |
---|---|
24 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |