Forum Discussion
Sum filtering minimum date (DAX)
Hello,
I have a question about how to get a sum by filtering the minimum date of a field.
Follow example
I have two tables (Calendar & Values),
Today I am filtering the "Date" field that is from the Calendar table and I make a sum of the "Value" field that is of the Values table. The problem is that I need this sum to only bring the value of the lowest selected date.
Calendar
Date(DD/MM/YYYY)
01/05/2019
02/05/2019
03/05/2019
04/05/2019
05/05/2019
Values
DateValue(DD/MM/YYYY), Value
02/05/2019, 2000
02/05/2019, 500
03/05/2019, 1000
04/05/2019, 40000
05/05/2019, 288
05/05/2019, 550
In this example above the return of the measure should be 2500, because I have the filtered dates from 01/05/2019 until 05/05/2019, but I only have date recorded in the table "Values" as of 02/05/2019.
Can someone help me?
Hello,
You can try this:
Measure_ =
VAR MinDateFromSelectedInterval = MIN('Calendar'[Date])
VAR MinAvailableDateWithValues = CALCULATE(MIN('Values'[Date]), 'Values'[Date] >= MinDateFromSelectedInterval)
RETURN
CALCULATE(SUM('Values'[Value]), FILTER('Values', 'Values'[Date] = MinAvailableDateWithValues))Regards,
ElenaN
3 Replies
Hi ,I have one requirement as follow:
Dates Target Value Expected Target Value
10/6/2019 09:54:00PM 11 11
10/6/2019 05:34:00PM 11 0
10/6/2019 03:27:00PM 11 0
11/6/2019 06:26:00PM 22 22
11/6/2019 02:55:00PM 22 0
Like for one date there must be one target to calculate SUM otherwise the SUM will give 33 for 10/6/2019 and 44 for 11/6/2019.
I was thinking to create a calculated measure to get the Expected Target value
IF(Min of date and if the Dates = Min of Dates , Target value = Target value , 0).
But when I create a measure for Minimum of Date it was giving me the same column as Dates. Please refer below:
Dates Minimum of Date Expected Minimum Date
10/6/2019 09:54:00PM 10/6/2019 09:54:00PM 10/6/2019 03:27:00PM
10/6/2019 05:34:00PM 10/6/2019 05:34:00PM 10/6/2019 03:27:00PM
10/6/2019 03:27:00PM 10/6/2019 03:27:00PM 10/6/2019 03:27:00PM
11/6/2019 06:26:00PM 11/6/2019 06:26:00PM 11/6/2019 02:55:00PM
11/6/2019 02:55:00PM 11/6/2019 02:55:00PM 11/6/2019 02:55:00PM
It will be a great help if you can suggest some way to achieve this.
I have used the following DAX to calculate the MIN of Dates:
Minimum of Date = CALCULATE(MIN('Table'[Dates]), GROUPBY('Table','Table'[Dates].[Date]))
Thanks in Advance!!
Regards,
Jyoti Sankar
Hello,
You can try this:
Measure_ =
VAR MinDateFromSelectedInterval = MIN('Calendar'[Date])
VAR MinAvailableDateWithValues = CALCULATE(MIN('Values'[Date]), 'Values'[Date] >= MinDateFromSelectedInterval)
RETURN
CALCULATE(SUM('Values'[Value]), FILTER('Values', 'Values'[Date] = MinAvailableDateWithValues))Regards,
ElenaN
