cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
ElenaN
Resolver V
Resolver V

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

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable


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
Resolver V
Resolver V

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors