Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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" act...
  • technolog's avatar
    technolog
    2 years ago

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

technolog's avatar
technolog
Super User
2 years ago

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