Help with a Measure DAX

[Elisa_Costanza]

Good morning! I need your help with a DAX measure. 

My data model is composed of:

• A fact table called "assunta_datamodel_vehicles_calendar", it contains multiple VINs over several Month/Year rows because a vehicle's warranty can extend over multiple months. which contains the following columns:
1. active_days
2. activity_rate
3. n_days
4. VIN_CD (key that links the fact table to the DIM table cv_datamodel_usecase_vehicles)
5. year_month (key that links the fact table to the DIM table cv_datamodel_calendar.

• A dimension table cv_datamodel_calendar, containing the calendar, with the following columns:
1. DAY_DT (date format)
2. MONTH_NM
3. WEEK
4. YEAR_MONTH (linked with the fact table)
5. YEAR_NM
6. YEAR_WEEK_CD

• Another dimension table"cv_datamodel_usecase_vehicles" that filters the fact table and contains the vehicle information (VIN is the unique vehicle key), with the following fields:
1. VIN_CD (unique key linking to the fact table)
2. Brand_cd
3. Division_cd
4. Base_warranty_start_date

So, I need to filter the fact table by both calendar and vehicle dimension table. I need a DAX measure that calculates the sum of activity_rate, considering only the vehicles whose warranty date is between the MaxDate selected from the calendar and -24 months.
So far, I’ve written the first part for handling calendar dates by creating two variables:
 
TEST_DATE =
VAR MaxDate = MAX(cv_datamodel_calendar[DAY_DT])
VAR MinDate = EOMONTH(MaxDate, -24)
RETURN
 
Now, I need to add the SUM of activity_rate, filtered for the vehicles that have a Base_warranty_start_date between MaxDate and MinDate. Keep in mind that this column is of type text and formatted like this: 2023-03-14 (yyyy-mm-dd).
As a first step, I create a column in date format so I can filter correctly while keeping the yyyy-mm-dd order, and then I create the  DAX measure "

TEST11 =
VAR MaxDate = MAX(cv_datamodel_calendar[DAY_DT])
VAR MinDate = EOMONTH(MaxDate, -24)
RETURN
CALCULATE(
SUM(assunta_datamodel_vehicles_calendar[activity_rate]),
FILTER(
assunta_datamodel_vehicles_calendar,

RELATED(cv_datamodel_usecase_vehicles[DataFormattata]) >= MinDate &&
RELATED(cv_datamodel_usecase_vehicles[DataFormattata]) <= MaxDate &&

assunta_datamodel_vehicles_calendar[year_month] >= FORMAT(MinDate, "YYYY-MM") &&
assunta_datamodel_vehicles_calendar[year_month] <= FORMAT(MaxDate, "YYYY-MM")
)
)
"

but after several test the formula does not work correctly. Can someone help me understand?

[v-sathmakuri · Accepted solution]

Hi @Elisa_Costanza ,

May I ask if you have resolved this issue? if not could you please share more details on the issue to provide better resolutions.

If you issue got resolved, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[bhanu_gautam]

@Elisa_Costanza First, ensure that the Base_warranty_start_date column in the cv_datamodel_usecase_vehicles table is correctly converted to a date format. You can create a calculated column for this:

DAX
DataFormattata = DATE(
LEFT(cv_datamodel_usecase_vehicles[Base_warranty_start_date], 4),
MID(cv_datamodel_usecase_vehicles[Base_warranty_start_date], 6, 2),
RIGHT(cv_datamodel_usecase_vehicles[Base_warranty_start_date], 2)
)

DAX
TEST11 =
VAR MaxDate = MAX(cv_datamodel_calendar[DAY_DT])
VAR MinDate = EOMONTH(MaxDate, -24)
RETURN
CALCULATE(
SUM(assunta_datamodel_vehicles_calendar[activity_rate]),
FILTER(
assunta_datamodel_vehicles_calendar,
RELATED(cv_datamodel_usecase_vehicles[DataFormattata]) >= MinDate &&
RELATED(cv_datamodel_usecase_vehicles[DataFormattata]) <= MaxDate &&
assunta_datamodel_vehicles_calendar[year_month] >= FORMAT(MinDate, "YYYY-MM") &&
assunta_datamodel_vehicles_calendar[year_month] <= FORMAT(MaxDate, "YYYY-MM")
)
)

[Elisa_Costanza]

Hi @bhanu_gautam , thank you for your feedback. These steps you explained are exactly how i did it, but they don't work

[bhanu_gautam]

Can you share what it is the issue you are facing