Forum Discussion
Date DIfference from current selection.
Hey Guys,
I am stuck with a new a problem now.
So i have a list of vehicles which has a column "active from"-From which date the vhicle is active
and "completion"-its just 6 months after the" active from" date.
Objective-When a user select a date range ,so first we have to check if any of the vehicle's completions date is falling under that date range.
IF Yes then we have to calculate the difference between last date of selection with the vehicle's completion date.Then that difference will be divided by30.
Contains = IF(CONTAINS(vehiclecount,ehiclecount[Completion Date],[selected_Date]),1,0)
Returns me if the vehicles completion date is under selection
Measure =
Var conta =[Contains]
Return
CALCULATE(DATEDIFF(MIN(vw_vehiclecount[6 Months Completion]),[selected_Date],DAY),FILTER(vw_vehiclecount,conta=1))/30
This gives me the total and divided by 30.
But i total is coming our wrong
To achieve your objective, let's refine your DAX logic to properly calculate the difference and ensure the totals are correctly calculated. We'll create a measure that checks if the completion date falls within the selected date range and then calculate the difference for those vehicles.
Measure to check if the completion date falls within the selected date range:
CompletionInRange =
IF(
COUNTROWS(
FILTER(
ALLSELECTED(vw_vehiclecount),
vw_vehiclecount[Completion Date] >= MIN(vw_vehiclecount[selected_Date]) &&
vw_vehiclecount[Completion Date] <= MAX(vw_vehiclecount[selected_Date])
)
) > 0,
1,
0
)
Measure to calculate the difference in days divided by 30:
Measure =
VAR LastSelectedDate = MAX(vw_vehiclecount[selected_Date])
RETURN
SUMX(
FILTER(
vw_vehiclecount,
vw_vehiclecount[Completion Date] >= MIN(vw_vehiclecount[selected_Date]) &&
vw_vehiclecount[Completion Date] <= LastSelectedDate
),
(DATEDIFF(vw_vehiclecount[Completion Date], LastSelectedDate, DAY) / 30)
)
1 Reply
To achieve your objective, let's refine your DAX logic to properly calculate the difference and ensure the totals are correctly calculated. We'll create a measure that checks if the completion date falls within the selected date range and then calculate the difference for those vehicles.
Measure to check if the completion date falls within the selected date range:
CompletionInRange =
IF(
COUNTROWS(
FILTER(
ALLSELECTED(vw_vehiclecount),
vw_vehiclecount[Completion Date] >= MIN(vw_vehiclecount[selected_Date]) &&
vw_vehiclecount[Completion Date] <= MAX(vw_vehiclecount[selected_Date])
)
) > 0,
1,
0
)
Measure to calculate the difference in days divided by 30:
Measure =
VAR LastSelectedDate = MAX(vw_vehiclecount[selected_Date])
RETURN
SUMX(
FILTER(
vw_vehiclecount,
vw_vehiclecount[Completion Date] >= MIN(vw_vehiclecount[selected_Date]) &&
vw_vehiclecount[Completion Date] <= LastSelectedDate
),
(DATEDIFF(vw_vehiclecount[Completion Date], LastSelectedDate, DAY) / 30)
)