Issue using Date measure to filter a summing measure

[nkillia]

I have created a Start Date measure to calculate the latest start date:

I am trying to use this date measure to filter data for a summing measure:

CALCULATE(

SUM(Data[Amount]),

FILTER(Data,Data[Date] >= [Start Date]))

 This measure is returning the Sum of the amount from all years.

CALCULATE(

SUM(Data[Amount]),

FILTER(Data,Data[Date] >=DATE(2019,1,1))

This version of the measure is returning the correct amount. 

Is there a way to filter the data using a Date measure?

[v-easonf-msft]

Hi ,@nkillia 

According to you description,  you can try to  add “all” function in you  your measures “Max Current Date” .

I don't have the formula of your measure “Max Current Date” .

So  here is  a simple sample  using some  other data:

Max Current Date = CALCULATE(MAX(financials[Date].[Date]),all(financials))

Max Current Date without all = MAX(financials[Date])

By comparison ,you will find the measure “Max Current Date” ignore any filters that might have been applied  .

If it doesn't meet your requirement, kindly share your sample data and expected result if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nkillia]

Hi @v-easonf-msft ,

Here is an example of what I am hoping to acomplish:
My MAX Date Formula is =Max('table'[Date])
My Start Date Formula is = Date(YEAR([Max Date]),1,1)
I am trying to sum only the data that has a date greater than 1/1/2019

[Anonymous]

Hi @nkillia 

Try this,

CALCULATE(SUM(Customer[Sales]),FILTER(Customer,Customer[Monthly].[Year]>=YEAR(MAX(Customer[Monthly]))))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[nkillia]

Hi @Anonymous ,

Thank you, is there a way to use the start date measure instead of YEAR(MAX(Customer[Monthly])) part?

[Anonymous]

Hi @nkillia 

Modify your measure

start date measure=YEAR(MAX(Customer[Monthly])) 

and use this measure in your filter part of formula .

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[nkillia]

This may be a better example of what I am trying to accomplish.

1. Return the max date of type A
        Max A Date =CALCULATE(MAX('Table'[Date]),'Table'[Type] <> "B")

2. Return start of the year for that date.

       A Start Year = DATE(Year([Max A Date]),1,1)

3. Return sum of the amount between those two dates excluding type B

Sum of Amount = CALCULATE(SUM('Table'[Amount]),'Table'[Type]<>"B",FILTER('Table', ('Table'[Date] >= [A Start Year] && 'Table'[Date] <= [Max A Date])))

This should be returning 10 but is returning 22.

The test data can be found below:

Date         Amount Type

1/1/2018	1	A
2/1/2018	1	A
3/1/2018	1	A
4/1/2018	1	A
5/1/2018	1	A
6/1/2018	1	A
7/1/2018	1	A
8/1/2018	1	A
9/1/2018	1	A
10/1/2018	1	A
11/1/2018	1	A
12/1/2018	1	A
1/1/2019	1	A
2/1/2019	1	A
3/1/2019	1	A
4/1/2019	1	A
5/1/2019	1	A
6/1/2019	1	A
7/1/2019	1	A
8/1/2019	1	A
9/1/2019	1	A
10/1/2019	1	A
1/1/2019	1	B
2/1/2019	1	B
3/1/2019	1	B
4/1/2019	1	B
5/1/2019	1	B
6/1/2019	1	B
7/1/2019	1	B
8/1/2019	1	B
9/1/2019	1	B
10/1/2019	1	B
11/1/2019	1	B
12/1/2019	1	B
1/1/2020	1	B
2/1/2020	1	B
3/1/2020	1	B
4/1/2020	1	B
5/1/2020	1	B
6/1/2020	1	B
7/1/2020	1	B
8/1/2020	1	B
9/1/2020	1	B
10/1/2020	1	B
11/1/2020	1	B
12/1/2020	1	B

[Anonymous · Accepted solution]

Hi @nkillia 

Modify your MAX_Date measure as below

MAX_Date = CALCULATE(MAX(TEST1[Date]),FILTER(all(TEST1),TEST1[Type]<>"B")).

 when we add "all" it takes your entire dataset irrespective of your filters.

PReviously we are simple checking with it with filters and it is evaluating  MAX Date for each row. 

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[nkillia]

@Anonymous Thank you, I believe that worked for me. 
Thank you again for all your time and effort. I truly appreciate it. 

[az38]

@nkillia 

try

CALCULATE(
SUM(Data[Amount]),
FILTER(ALL(Data),Data[Date] >= SELECTEDVALUE([Start Date])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[nkillia]

Hi @az38,

I tried the following and received this error: "Column 'Start Date' cannot be found or may not be used in this expression"

Is there any expression that would be able to grab the value of the measure?