Current year (12 months) and Prior Year (12 months) excluding current month

[MITeam]

I am struggling with this one - I can do current year and prior for days but for the financials I need months and not include the current month so we dont get hals months at the start and end.

I have a table with weekending dates and the month it relates and this table is linked to the main invoice data set.

In the dates table I need to say for example (in Excel):

=IF(AND(V2<EOMONTH(TODAY(),-1)+1,V2>EDATE(EOMONTH(TODAY(),-1)+1,-13)),"Current Year",IF(AND(V2<EDATE(EOMONTH(TODAY(),-1)+1,-12),V2>EDATE(EOMONTH(TODAY(),-1)+1,-25)),"Prior Year","-"))

My table is called "Month Mapping" and simply has 3 columns:

Any help truly appreciated

[v-yangliu-msft · Accepted solution]

Hi  @MITeam ,

Here are the steps you can follow：

1. Create calculated column.

Column =
var _today=TODAY()
var _currentend=
EOMONTH(
    _today,-1)
var _currentstart=
EOMONTH(
    _today,-13)
var _prioryear=
EOMONTH(
    _currentstart,-12)
return
IF(
    'Month Mapping'[W/E] >_prioryear&&'Month Mapping'[W/E] <=_currentstart,
    "Prior Year",
    IF(
        'Month Mapping'[W/E]>_currentstart&&'Month Mapping'[W/E]<=_currentend,"Current Year",
       "-"
))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[MITeam]

Thanks Liu. I realised that the excel formula I put in asking for help worked lol.. People must have read and thought I was a plank:

_CY/PY Full = IF(AND('02. Dates'[_Mmm-yy]<EOMONTH(TODAY(),-1)+1,'02. Dates'[_Mmm-yy]>EDATE(EOMONTH(TODAY(),-1)+1,-13)),"Current Year",IF(AND('02. Dates'[_Mmm-yy]<EDATE(EOMONTH(TODAY(),-1)+1,-12),'02. Dates'[_Mmm-yy]>EDATE(EOMONTH(TODAY(),-1)+1,-25)),"Prior Year","-"))

And for prior month /pp month and pppm:

_CY/PY/PPY Month = IF(AND('02. Dates'[_Mmm-yy]<EOMONTH(TODAY(),-1)+1,'02. Dates'[_Mmm-yy]>EDATE(EOMONTH(TODAY(),-1)+1,-2)),[_Mmm-yy],IF(AND('02. Dates'[_Mmm-yy]<EDATE(EOMONTH(TODAY(),-1)+1,-12),'02. Dates'[_Mmm-yy]>EDATE(EOMONTH(TODAY(),-1)+1,-14)),[_Mmm-yy],IF(AND('02. Dates'[_Mmm-yy]<EDATE(EOMONTH(TODAY(),-1)+1,-24),'02. Dates'[_Mmm-yy]>EDATE(EOMONTH(TODAY(),-1)+1,-26)),[_Mmm-yy],0)))

[MITeam]

Anid if a genius can also give me another column to go with this of Last Month and Prior Year Last month so currently it would give Feb 2023 and Feb 2022 I think I would cry with Happiness.