topic Re: Help with a Measure DAX in DAX Commands and Tips

Help with a Measure DAX

Elisa_Costanza — Thu, 10 Apr 2025 10:57:12 GMT

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:

active_days
activity_rate
n_days
VIN_CD (key that links the fact table to the DIM table cv_datamodel_usecase_vehicles)
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:

DAY_DT (date format)
MONTH_NM
WEEK
YEAR_MONTH (linked with the fact table)
YEAR_NM
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:

VIN_CD (unique key linking to the fact table)
Brand_cd
Division_cd
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?

Re: Help with a Measure DAX

bhanu_gautam — Thu, 10 Apr 2025 11:21:18 GMT

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

Re: Help with a Measure DAX

Elisa_Costanza — Thu, 10 Apr 2025 11:31:41 GMT

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

Re: Help with a Measure DAX

bhanu_gautam — Thu, 10 Apr 2025 11:37:56 GMT

Can you share what it is the issue you are facing

Re: Help with a Measure DAX

v-sathmakuri — Tue, 15 Apr 2025 14:02:33 GMT

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.