Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 f...
Need Help
  • ElenaN's avatar
    ElenaN
    7 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
7 years ago


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