Forum Discussion

Elisa_Costanza's avatar
1 year ago
Solved

Help with a Measure DAX

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?

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

     

4 Replies

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

     

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User

        Can you share what it is the issue you are facing

         

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support

    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.