Dinamic date calculation based on a max value of a slicer

[Anonymous]

Hello

I have the following situation:

A slicer with dates using a Calendar table.

A history table that has a Start and End date that is always filled.

I need to calculate the number of hours between the start date and the Max value of the slicer if the end date is higher than the selected date or just use the date of the selector minus the end date.

I manage to get the max value of the slicer via a measure (MAXX), but when I try to use it to make the calculation or show it on a table I always get the higher date of the table. For example if the slicer has 31 of may of 2017, in the table I get 31 of December  of 2017.

I was assuming the row context in this case should not be applied since the table is filter by the slicer.

What am I doing wrong?

Thanks

[v-jiascu-msft]

Hi @Anonymous,

Can you share your PBIX file please? It's hard to say what is wrong. At least share the relationship of your model and the formulas.

Best Regards!

Dale

[Anonymous]

Hello

What I have is the following model:

Calendar aging = CALENDARAUTO();

Time in status = DATEDIFF('changegroup (4)'[Start Date];'changegroup (4)'[End Date];HOUR);

MaxDate = MAXX('Calendar aging';'Calendar aging'[Date])

Dashboard:

Formula to test:
Time in Status on a Date =
IF([MaxDate]<'changegroup (4)'[End Date];DATEDIFF('changegroup (4)'[End Date];[MaxDate];DAY);0)

Has you can see it always gives 0. There is no relation between Calendar table and this one (I can not make one).

Regards

[v-jiascu-msft]

Hi @Anonymous,

The possible cause is you created "Time in Status on a Date" as a Calculated Column instead of a measure. Several reasons.

1. You can't use a table column in a measure directly like this:

[MaxDate]<'changegroup (4)'[End Date];

2. The first parameter of Datediff must be smaller than the second. There could be an error in your formula. 

3. If this formula is a Calculated Column, the [MaxDate] will always return the max date of the table "Calendar aging".

Maybe you can try this formula as a measure.

Time in Status on a Date =
IF (
    [MaxDate] > MIN ( 'changegroup (4)'[End Date] );
    DATEDIFF ( MIN ( 'changegroup (4)'[End Date] ); [MaxDate]; DAY );
    0
)

Best Regards!

Dale