Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.