topic Dynamically calculate max and min time based on time slicer in DAX Commands and Tips

Dynamically calculate max and min time based on time slicer

Anonymous — Mon, 02 Mar 2020 07:24:36 GMT

Hello Need help with DAX. I am trying to calculate max time and min time of the submissions made. from the submission date.

I created a measure to calculate the max time and min time using the below syntax.

Min time =

CALCULATE(

MIN(Baseline_Append_new_group_index[Submission date]) ,

ALLEXCEPT(Baseline_Append_new_group_index ,Baseline_Append_new_group_index[Index]))

I want this max and min time to be dynamic with the submission date which I am using in the slicer. I want to filter the records which would calculate the max and min time which falls into the slicer range. I am using max and min time to make further calculations of Delta of values between the MAX and MIN time. Currently, MAX and MIN time are static, how to make them dynamic with the slicer selection.

Please find below the snapshot of data.

I am using submission date in the slicer. I need the min time and max time to be calculated dynamically based on the date range selected in the slicer. I have been struggling with this since past few days and any help will be apprecaiated. Thanks in advance.

Re: Dynamically calculate max and min time based on time slicer

Anonymous — Mon, 02 Mar 2020 08:05:48 GMT

Create one date dimension

Calender(date(2016,1,1),date(2020,12,31))

Use this date dimension in slicer

Modify measure

Min time =

CALCULATE(

MIN(Baseline_Append_new_group_index[Submission date]) ,

ALLEXCEPT(Baseline_Append_new_group_index ,Baseline_Append_new_group_index[Index])
Filter(Baseline_Append_new_group_index,Baseline_Append_new_group_index[Submission date]>=min(Date[calender date]) && Baseline_Append_new_group_index[Submission date] <=max(Date[calender date])))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Dynamically calculate max and min time based on time slicer

Anonymous — Mon, 02 Mar 2020 15:50:58 GMT

I tried the solution, but it gives me the same max time and min time as the submission date as shown below.

As you can see in the image, after trying the solution, it gives the same min time and max time as the submission date. I can give you a used case, Lets say they make 10 submissions in the given time frame we select in the slicer. I need a submission closest to the start date of the slicer, and one closest to the end date of the slicer. Then I need to use the 2 submissions to calculate delta of a measure between those 2 submissions.

I have used the below code to calculate the max time and min time as mentioned earlier.

Min time = CALCULATE( MIN(Baseline_Append_new_group_index[Submission date]) , ALLEXCEPT(Baseline_Append_new_group_index ,Baseline_Append_new_group_index[Index]), FILTER(Baseline_Append_new_group_index,Baseline_Append_new_group_index[Submission date] >= MIN('Date'[Date]) && Baseline_Append_new_group_index[Submission date] <= MAX('Date'[Date])))

for max time,

Max time = CALCULATE( MAX(Baseline_Append_new_group_index[Submission date]) , ALLEXCEPT(Baseline_Append_new_group_index ,Baseline_Append_new_group_index[Index]), FILTER(Baseline_Append_new_group_index,Baseline_Append_new_group_index[Submission date] >= MIN('Date'[Date]) && Baseline_Append_new_group_index[Submission date] <= MAX('Date'[Date])))

then I use these time stamps to calulate the difference between the 2 submissions as below.

Net Head Count Delta(Index) = CALCULATE(SUM(Baseline_Append_new_group_index[Net Head Count Change]),FILTER(Baseline_Append_new_group_index,Baseline_Append_new_group_index[Submission date] = [Max time])) -CALCULATE(SUM(Baseline_Append_new_group_index[Net Head Count Change]),FILTER(Baseline_Append_new_group_index,Baseline_Append_new_group_index[Submission date] = [Min time]))

I am just missing something very simple, or it is more complicated to acheive the results. All the help is highly appreciated.

Thanks in Advance.

Bharat

Re: Dynamically calculate max and min time based on time slicer

Anonymous — Tue, 03 Mar 2020 08:25:17 GMT

Could you share your dataset in text?

Copy it in excel and copy it from there and paste here.

The measure which i have suggested should work.

You want max and min date per index for slicer selected date right?

Thanks,

Pravin

Re: Dynamically calculate max and min time based on time slicer

Anonymous — Tue, 03 Mar 2020 08:39:46 GMT

try this

Min time =
CALCULATE(
MIN(Baseline_Append_new_group_index[Submission date]) ,
ALLEXCEPT(Baseline_Append_new_group_index ,Baseline_Append_new_group_index[Index],Baseline_Append_new_group_index[Account name],Baseline_Append_new_group_index[offering]),
FILTER(Baseline_Append_new_group_index,Baseline_Append_new_group_index[Submission date] >= MIN('Date'[Date]) && Baseline_Append_new_group_index[Submission date] <= MAX('Date'[Date])))

similarly add other columns in allexcept function for max time.

Thanks,

Pravin