topic Forcing measure constant value after end_date in DAX Commands and Tips

Forcing measure constant value after end_date

niziris — Fri, 16 Feb 2024 09:49:16 GMT

In my table, I have different components with start_date and end_date. There is a column with component_active_dates. Active dates are connected from Calendar table.

Also there is another table with mileage for different cars that are connected to components.

My problem is because I slice mileage on component start and end date, when I plot component mileage it goes to 0 after end date.
This mean that in the sum of mileage on all components there is a dip.

What I need is to push the last mileage on the end date, further till the end of Calendar.

In the code below, when I use __convert it fills in for all Calendar dates, not just after the component_end, but if I put some number (for instance 500) then the whole thing is wokring and there is a flat line from compoenent_end till end of Calendar.
I try to mimic simple number with casting measure as double but it didnt help.
Any advice?

Thank you for your time

VAR __mileage_on_last_date = CALCULATE ( MAX ( ft_aggregated[totalvehicledistancehighres_max] ), 'Calendar'[Date] = __component_end) VAR __convert = CONVERT(__mileage_on_last_date, DOUBLE) VAR __after_end_date = IF ( TODAY() > __component_end, CALCULATE ( __convert, FILTER (ALL ( 'Calendar' ), 'Calendar'[Date] >= __component_end) ), BLANK () )

Re: Forcing measure constant value after end_date

123abc — Fri, 16 Feb 2024 10:10:18 GMT

It seems like you're trying to handle the scenario where the mileage for a component remains constant after its end date until the end of the calendar period. You want to ensure that when you plot the mileage of components, it doesn't drop to zero after the end date but rather stays constant until the end of your calendar.

Your approach is mostly correct, but you're encountering issues with the calculation logic. Let's refine your DAX code:

VAR __mileage_on_last_date =
CALCULATE (
MAX ( ft_aggregated[totalvehicledistancehighres_max] ),
FILTER (ft_aggregated, ft_aggregated[component_active_dates] <= MAX('Calendar'[Date]))
)

VAR __after_end_date =
IF (
TODAY() > __component_end,
__mileage_on_last_date,
BLANK()
)

RETURN
__after_end_date

Here's what each part of the DAX code does:

__mileage_on_last_date: This variable calculates the maximum mileage for the component until the last date available in the calendar. This is achieved by filtering the mileage data up to the maximum date available in the calendar.
__after_end_date: This variable checks if the current date is greater than the component's end date. If it is, it returns the value of __mileage_on_last_date, effectively making the mileage constant after the end date. Otherwise, it returns BLANK().
Finally, the RETURN statement returns the value of __after_end_date.

Make sure that your ft_aggregated table is properly filtered to include only the relevant mileage data for the component you are analyzing. Adjust the filters and relationships according to your data model if needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Forcing measure constant value after end_date

niziris — Fri, 08 Mar 2024 08:19:35 GMT

Thank you 123abc for your time and effort! The solution was very close to what you said and you were on track to take MAX('Calendar'[Date])) as a limit.
I will mark this as a Solution.

Sorry for the delay in my response.