Measure with condition based on date slicer

[FloSeur]

Hello. I'm trying to make a tricky powerbi dax measure and I'm a bit lost right know because it's been 2 days i'm struggling on this. 

I have the following data model in my db:

task_id	agent_id	imputation_date	exceeding_date	value_before_exceeding	value_after_exceeding
id_1	592	2020-12-17		32.63235	23.30882
id_1	592	2021-01-20		32.63235	23.30882
id_1	592	2021-02-03		32.63235	23.30882
id_1	592	2021-02-08		32.63235	23.30882
id_1	362	2021-03-12		130.52941	93.23529
id_1	362	2021-04-12	2021-04-12		559.41177
id_1	649	2021-04-12	2021-04-12		559.41177

In my powerbi dashboard, i'm trying to display this data in a table with a date slicer (based on imputation_date) and following these conditions:

1. If my maximum date range is above exceeding_date: i want to display the sum (for each group of agent and task)  of value_after_exceeding
2. Else i want to display the sum of value_before_exceeding
3. In both case, i want the sum for the date range based on date_imputation

I create a table based on min and max imputation_date:

calendar_imputation = CALENDAR(MIN(valo_fact_imputation[date_imputation]), MAX(valo_fact_imputation[date_imputation]))

My test visual look like this:

(visual 1)

(visual 2)

I have mesaures for min and max slicer date:

min_slicer_date = CALCULATE(FIRSTDATE(calendar_imputation[Date]))

max_slicer_date = CALCULATE(LASTDATE(calendar_imputation[Date]))

And so far my measure to calculate date looks like this:

sales = 
VAR grouped_by_filter_date =
SUMMARIZE(
    FILTER(valo_fact_imputation,
        valo_fact_imputation[date_imputation] >= [min_slicer_date] && valo_fact_imputation[date_imputation] <= [max_slicer_date]
    ),
    valo_fact_imputation[task_id],
	valo_fact_imputation[agent],
    "exceeding", CALCULATE(LASTDATE(valo_fact_imputation[exceeding_date]), ALLEXCEPT(valo_fact_imputation, valo_fact_imputation[task_id], valo_fact_imputation[agent]))
)
return CALCULATE(
        IF(
            COUNTAX(grouped_by_filter_date, [exceeding]) > 0,
            SUMX(
                SUMMARIZE(
                    grouped_by_filter_date,
                    valo_fact_imputation[task_id],
                    valo_fact_imputation[agent],
                    "sum_valo_imput", CALCULATE(SUM(valo_fact_imputation[value_after_exceeding]))),
                [sum_valo_imput]
            ),
            SUMX(
                SUMMARIZE(
                    grouped_by_filter_date,
                    valo_fact_imputation[task_id],
                    valo_fact_imputation[agent],
                    "sum_valo_imput", CALCULATE(SUM(valo_fact_imputation[value_before_exceeding]))), 
                [sum_valo_imput]
            )
        )
)

 My problem is that on visual 1, the sum is made on valo_afeter_exceeding even if it looks like there is no value my "exceeding" column from my SUMMARIZE method.

Any help appreciated and don't hesitate to ask for more infos.

[FloSeur · Accepted solution]

I managed to do what I wanted, here the final measure for those who might be interested:

ca_reel_agent_v2 = 
VAR grouped_by_filter_date = 
SUMMARIZE(
    FILTER(fact_valo_imputation,
        fact_valo_imputation[date_imputation] >= [min_slicer_date] && fact_valo_imputation[date_imputation] <= [max_slicer_date]
    ),
    fact_valo_imputation[task_id],
    fact_valo_imputation[agent],
    "min_imputation", CALCULATE(MIN(fact_valo_imputation[date_imputation]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id], fact_valo_imputation[agent])),
	"max_imputation", CALCULATE(MAX(fact_valo_imputation[date_imputation]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id], fact_valo_imputation[agent])),
    "exceeding", CALCULATE(FIRSTDATE(fact_valo_imputation[exceeding_date]), ALLEXCEPT(fact_valo_imputation, fact_valo_imputation[task_id]))
)
return CALCULATE(
    IF(
        COUNTAX(FILTER(grouped_by_filter_date, [exceeding] >= [min_slicer_date] && [exceeding] <= [max_slicer_date]), [exceeding]) > 0,
        SUMX(
            SUMMARIZE(
                grouped_by_filter_date,
                "sum_valo_imput", CALCULATE(SUM(fact_valo_imputation[value_after_exceeding]))),
            [sum_valo_imput]
        ),
        SUMX(
            SUMMARIZE(
                grouped_by_filter_date,
                "sum_valo_imput", CALCULATE(SUM(fact_valo_imputation[value_before_exceeding]))), 
            [sum_valo_imput]
        )
    )
)

Thanks @lbendlin for your time on the topic.

[lbendlin]

If my maximum date range is above exceeding_date: i want to display the sum (for each group of agent and task)  of value_after_exceeding

please describe what should happen when the exceeding_date is empty.

Do you want to display the result by agent?

[FloSeur]

When exceeding_date is empty, the task is not considered as late so yes, it should display the sum of value_before_exceeding for each agent.

In the example dataset, I didn't fill the row of exceeding_date column with null value but it appears like this in db.

For a bit of context, each row correspond to allocation with amount of duration (i didn't put the column in dataset) and when the cumulative sum of allocation exceed task time estimation, i start to fill the column exceeding_date.

I hope my explanations are clear.