Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Good morning! I need your help with a DAX measure.
My data model is composed of:
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?
Solved! Go to 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.
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.
@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")
)
)
Proud to be a Super User! |
|
Hi @bhanu_gautam , thank you for your feedback. These steps you explained are exactly how i did it, but they don't work
Can you share what it is the issue you are facing
Proud to be a Super User! |
|
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
11 | |
10 | |
10 | |
9 |
User | Count |
---|---|
18 | |
13 | |
12 | |
11 | |
8 |