Always return latest month last date value for the last 6 years

[dokat]

Hi Everyone,

I am trying to pull the Latest Month (YTD) value for the last 6 years. My table looks like below. I'd like to return corresponding value for 6/30/2017,6/30/2018,6/30/2019,6/30/2020,6/30/2021,6/30/2022.

next month will be 7/31/2017 and so on. 

Is there anyway to write a formula where it returns values for always the latest month ending date for the past 6 years.

Calendar Year

12/31/2017

12/31/2018

12/31/2019

12/31/2020

12/31/2021

4/1/2017

4/1/2018

4/1/2019

4/1/2020

4/1/2021

4/1/2022

4/30/2017

4/30/2018

4/30/2019

4/30/2020

4/30/2021

4/30/2022

6/30/2017

6/30/2018

6/30/2019

6/30/2020

6/30/2021

6/30/2022

6/1/2017

6/1/2018

6/1/2019

6/1/2020

6/1/2021

6/1/2022

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

monthenddate last six years = 
VAR _endofmonthtoday =
    EOMONTH ( TODAY (), 0 )
VAR _endofmonthnumber =
    MONTH ( _endofmonthtoday )
VAR _endofmonthdatenumber =
    DAY ( _endofmonthtoday )
VAR _currentyear =
    YEAR ( TODAY () )
VAR _sixyearsbefore = _currentyear - 5
RETURN
    FILTER (
        'Calendar',
        YEAR ( 'Calendar'[Date] ) >= _sixyearsbefore
            && YEAR ( 'Calendar'[Date] ) <= _currentyear
            && MONTH ( 'Calendar'[Date] ) = _endofmonthnumber
            && DAY ( 'Calendar'[Date] ) = _endofmonthdatenumber
    )

[v-yalanwu-msft · Accepted solution]

Hi, @dokat ;

You could create a new table :

New Date = 
var _maxdate=EOMONTH(MAX('Table'[Calendar Year]),0)
var _mindate=EOMONTH(MAX('Table'[Calendar Year]),-12*5)
return FILTER(CALENDAR(_mindate,_maxdate),FORMAT([Date],"mm-dd")=FORMAT(_maxdate,"mm-dd"))

The final show:

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

[v-yalanwu-msft · Accepted solution]

Hi, @dokat ;

You could create a new table :

New Date = 
var _maxdate=EOMONTH(MAX('Table'[Calendar Year]),0)
var _mindate=EOMONTH(MAX('Table'[Calendar Year]),-12*5)
return FILTER(CALENDAR(_mindate,_maxdate),FORMAT([Date],"mm-dd")=FORMAT(_maxdate,"mm-dd"))

The final show:

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

[dokat · Accepted solution]

@v-yalanwu-msft  Thank you it worked

[Ashish_Mathur]

Hi,

Your end objective is not clear.  Do you have sales figures there in another column or another other numeric column that you want to analyse.  Share some more information and show the expected result.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

monthenddate last six years = 
VAR _endofmonthtoday =
    EOMONTH ( TODAY (), 0 )
VAR _endofmonthnumber =
    MONTH ( _endofmonthtoday )
VAR _endofmonthdatenumber =
    DAY ( _endofmonthtoday )
VAR _currentyear =
    YEAR ( TODAY () )
VAR _sixyearsbefore = _currentyear - 5
RETURN
    FILTER (
        'Calendar',
        YEAR ( 'Calendar'[Date] ) >= _sixyearsbefore
            && YEAR ( 'Calendar'[Date] ) <= _currentyear
            && MONTH ( 'Calendar'[Date] ) = _endofmonthnumber
            && DAY ( 'Calendar'[Date] ) = _endofmonthdatenumber
    )

[dokat]

@Jihwan_Kim 

Thank you it worked

[dokat]

Hi,

Thanks for sharing the formulas. My dates will be hard coded in date table.  is there way to bypass EOMONTH ( TODAY (), 0 ) function.  For ex: I could be pulling june numbers (6/30) in Sep, or Oct or Now...if i use the function it will always look for the current month and not the latest month in data table.