Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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
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
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
Create one date dimension
Calender(date(2016,1,1),date(2020,12,31))
Use this date dimension in slicer
Modify measure
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |