topic Re: Time Slicer combined with a measure based on MAX date in DAX Commands and Tips

Time Slicer combined with a measure based on MAX date

Berl21 — Mon, 01 May 2023 19:10:51 GMT

Hi,

I am trying to put a visual together where ony the turnover of the latest date per ID gets reported.

To do this, I wrote the following measure:

Latest Test =

VAR _Summarize=
SUMMARIZE(
'Partition_Rank_SQL',
'Partition_Rank_SQL'[DimOpportunityId],
'Partition_Rank_SQL'[ID_Row],
"MININDEX",
CALCULATE(
MAX(Partition_Rank_SQL[Last_Modified]),
ALLSELECTED('Partition_Rank_SQL'[DimOpportunityId])
),
"LATESTENTRIES",
CALCULATE
(
SUM(Partition_Rank_SQL[Turnover]),
FILTER('Partition_Rank_SQL','Partition_Rank_SQL'[Last_Modified]= MAX(Partition_Rank_SQL[Last_Modified]))
)
)
RETURN
SUMX(_Summarize, [LATESTENTRIES])

Then I added a slicer based on "between", thinking that the latest modification would be taken into account thanks to the MAX statement written in the measure. The calendar table is directly linked to last modified as a column (date= last modified)

But this still continues to give me all entries between the two dates...where is the problem in the statement?

To be clear, in this example I should have only the value for the 17th of February. I should see the entry for the 14th only if I put the 14th, 15th or 16th of February as my upper limit in my slicer. The goal is to then sum up the turnover of all the latest entries for every id according to the slicer.

Thanks,

Pauline.

Re: Time Slicer combined with a measure based on MAX date

lbendlin — Tue, 02 May 2023 23:52:23 GMT

Then I added a slicer based on "between", thinking that the latest modification would be taken into account thanks to the MAX statement written in the measure.

How should your data model know that? Did you add the measure as a visual filter to the slicer?

Re: Time Slicer combined with a measure based on MAX date

Berl21 — Wed, 03 May 2023 09:38:19 GMT

Hi lbendlin

in the slicer I use the date column from my calendar table. But "date" is connected to "last_modified" from the data table. This should not be an issue?

Re: Time Slicer combined with a measure based on MAX date

lbendlin — Wed, 03 May 2023 11:12:10 GMT

It likely is an issue as that constricts the filter context to existing fact dates. You may need to use a disconnected table for the slicer.