Filter Date column by lastdate of other column date

[Anonymous]

Hello everyone, hoping for you and your family safety nowadays,

Is it possible to create a measure that I will filter the date of one column based on the last date available from another column? The purpose of this formula is to compare two values from the same day. Below is my formula Sample;

Sample = CALCULATE(SUM('All'[Value]),'All'[Year]=2019,'All'[Sameday]=LASTDATE('All'[Date]))

[v-kelly-msft]

Hi  @Anonymous ,

Sorry for the late reply.Are the 2 columns in the same table?If not,does the 2 tables have relationships betwee?Can you provide more details such as sample data and the expected output to make your require more clear?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello @v-kelly-msft ,

Yes, two columns are in the same table.

I have a table with 4 date columns and 1 value as shown below;

Original Date	Same Year	Same Day	Year	Value
January 1, 2020	January 1, 2020	January 1, 2020	2020	231
January 2, 2020	January 2, 2020	January 2, 2020	2020	214
January 3, 2020	January 3, 2020	January 3, 2020	2020	321
January 4, 2020	January 4, 2020	January 4, 2020	2020	531
January 5, 2020	January 5, 2020	January 5, 2020	2020	613
January 1, 2019	January 1, 2020	December 31, 2019	2019	61
January 2, 2019	January 2, 2020	January 1, 2020	2019	35
January 3, 2019	January 3, 2020	January 2, 2020	2019	213
January 4, 2019	January 4, 2020	January 3, 2020	2019	534
January 5, 2019	January 5, 2020	January 4, 2020	2019	742
January 6, 2019	January 6, 2020	January 5, 2020	2019	3
January 7, 2019	January 7, 2020	January 6, 2020	2019	6,342

I want to Sum up the value column by YTD, MTD, or Last date by the same day or same year column which will always be based on the last date of the original column. I tried some measure but it is not working;

MTD = CALCULATE(SUM('Table'[Value]),YEAR('Table'[Year])=2019,DATESMTD('Table'[Same year]))

the above formula is working but, I want to filter that I should be only until January 5, 2020 (or which is the latest date from the original date column) because all data for 2019 is included in the table.

[v-kelly-msft · Accepted solution]

Hi @Anonymous 

Sorry again for the late reply!

Pls see the solution below：

1. Create a measure as below:

Measure = 
var _maxdate=MAXX(ALL('Original Date Same Year Same Day Year ValueTable'),'Original Date Same Year Same Day Year ValueTable'[Original Date])
Return
CALCULATE(SUM('Original Date Same Year Same Day Year ValueTable'[Value]),FILTER('Original Date Same Year Same Day Year ValueTable','Original Date Same Year Same Day Year ValueTable'[Year]=2019&&'Original Date Same Year Same Day Year ValueTable'[Same Year]<=_maxdate))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  Thank you so much, it worked.

[v-kelly-msft]

Hi @Anonymous 

Sorry again for the late reply!

Pls see the solution below：

1. Create a measure as below:

Measure = 
var _maxdate=MAXX(ALL('Original Date Same Year Same Day Year ValueTable'),'Original Date Same Year Same Day Year ValueTable'[Original Date])
Return
CALCULATE(SUM('Original Date Same Year Same Day Year ValueTable'[Value]),FILTER('Original Date Same Year Same Day Year ValueTable','Original Date Same Year Same Day Year ValueTable'[Year]=2019&&'Original Date Same Year Same Day Year ValueTable'[Same Year]<=_maxdate))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Up

[Anonymous]

Help please.

[Anonymous]

Up

[Anonymous]

Help!