Sum filtering minimum date (DAX)

[Anonymous]

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?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[ElenaN · Accepted solution]

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