Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
AB2
Frequent Visitor

Looking for assistance on Dax for report building

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 CoderDealer NameVehicle Identification Number (VIN)Model YearSale DateUnits Sold
123456123456 Toyota1123202205/20221
123459123459 Toyota1128202205/20221
123462123462 Toyota1133202207/20221
123465123465 Toyota1138202304/20231
123468123468 Toyota1143202209/20221
123471123471 Toyota1148202206/20221
123474123474 Toyota1153202206/20221
123477123477 Toyota1158202205/20221
123480123480 Toyota1163202206/20221
123483123483 Toyota1168202208/20221
123486123486 Toyota1173202211/20221
123489123489 Toyota1178202205/20221
123492123492 Toyota1183202206/20221
123495123495 Toyota1188202206/20221
123498123498 Toyota1193202207/20221

 

 

Service History

Dealer CodeDealer NameService VINModel YearService RO NumberRO DateTotal RO's
123456123456 Toyota11232022SA2601303/20221
123459123459 Toyota11282022ST8317603/20201
123462123462 Toyota11332022ST8562805/20201
123465123465 Toyota11382022SA0062812/20201
123468123468 Toyota11432022SA0875005/20211
123471123471 Toyota11482022SA2056912/20211
123474123474 Toyota11532022SA3153506/20221
123477123477 Toyota11582022SA4299401/20231
123480123480 Toyota11632022ST7906701/20201
123483123483 Toyota11682022ST9309208/20201
123486123486 Toyota11732022SA2055912/20211
123489123489 Toyota11782022SA4013311/20221
123492123492 Toyota11832022SA0567604/20211
123495123495 Toyota11882022SA2117312/20211
123498123498 Toyota11932022SA2682904/20221
123456123456 Toyota11232022ST9957712/20201
123459123459 Toyota11282022SA0677004/20211
123462123462 Toyota11332022SA1866711/20211
123465123465 Toyota11382022SA2513703/20221
123468123468 Toyota11432022SA2870905/20221
123471123471 Toyota11482022SA3555409/20221
123474123474 Toyota11532022SA3824810/20221
123477123477 Toyota11582022ST9637510/20201
123480123480 Toyota11632022SA0254402/20211
1 ACCEPTED 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.

 

Wilson__0-1682962769984.png


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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

7 REPLIES 7
AB2
Frequent Visitor

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 CoderDealer NameVehicle Identification Number (VIN)Model YearSale DateUnits SoldServiced with 8 mths
123456123456 Toyota1123202205/202211
123459123459 Toyota1128202205/202211
123462123462 Toyota1133202207/202211
123465123465 Toyota1138202304/202310
123468123468 Toyota1143202209/202210
123471123471 Toyota1148202206/202210
123474123474 Toyota1153202206/202211
123477123477 Toyota1158202205/202210
123480123480 Toyota1163202206/202211
123483123483 Toyota1168202208/202211
123486123486 Toyota1173202211/202211
123489123489 Toyota1178202205/202211
123492123492 Toyota1183202206/202211
123495123495 Toyota1188202206/202211
123498123498 Toyota1193202207/20221

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





AB2
Frequent Visitor

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 CodeDealer NameService VINModel YearService RO NumberRO DateTotal RO's
123456123456 Toyota11232022SA2601301/01/231
123459123459 Toyota11282022ST8317602/01/231
123462123462 Toyota11332022ST8562803/01/231
123465123465 Toyota11382022SA0062806/01/231
123468123468 Toyota11432022SA0875005/01/231
123471123471 Toyota11482022SA2056905/01/231
123474123474 Toyota11532022SA3153501/01/231
123477123477 Toyota11582022SA4299401/01/231
123480123480 Toyota11632022ST7906702/01/231
123483123483 Toyota11682022ST9309201/01/231
123486123486 Toyota11732022SA2055912/01/231
123489123489 Toyota11782022SA4013303/01/231
123492123492 Toyota11832022SA0567601/01/231
123495123495 Toyota11882022SA2117301/01/231
123498123498 Toyota11932022SA2682902/01/231
123456123456 Toyota11232022ST9957701/01/231
123459123459 Toyota11282022SA0677002/01/231
123462123462 Toyota11332022SA1866706/01/231
123465123465 Toyota11382022SA2513706/01/231
123468123468 Toyota11432022SA2870905/01/231
123471123471 Toyota11482022SA3555406/01/231
123474123474 Toyota11532022SA3824801/01/231
123477123477 Toyota11582022ST9637501/01/231
123480123480 Toyota11632022SA0254402/01/231

Vehicles Sales

Dealer CoderDealer NameVehicle Identification Number (VIN)Model YearSale DateUnits Sold
123456123456 Toyota1123202205/20221
123459123459 Toyota1128202205/20221
123462123462 Toyota1133202207/20221
123465123465 Toyota1138202304/20231
123468123468 Toyota1143202209/20221
123471123471 Toyota1148202206/20221
123474123474 Toyota1153202206/20221
123477123477 Toyota1158202205/20221
123480123480 Toyota1163202206/20221
123483123483 Toyota1168202208/20221
123486123486 Toyota1173202211/20221
123489123489 Toyota1178202205/20221
123492123492 Toyota1183202206/20221
123495123495 Toyota1188202206/20221
123498123498 Toyota1193202207/20221

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.

 

Wilson__0-1682962769984.png


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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





AB2
Frequent Visitor

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. 😄




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Wilson_
Super User
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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.