cancel
Showing results for
Did you mean:
Regular Visitor

formula for converting year & month from text to make comparison

Hi

I need support in converting my data which consist of months and year e.g. as below

01 2017

02 2017

03 2017

04 2017 & so far and so good &

01 2018

02 2018

03 2018

04 2018 in same way

the support what i need is to convert 2017 as Last year (LY) & 2018 as current year (CY) in order to compare month 1 CY vs LY.

regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

Re: formula for converting year & month from text to make comparison

Not 100% on what you are going for here but perhaps this:

`Column = IF(RIGHT([Column1],4)="2017","LY","CY")`

Proud to be a Datanaut!

Highlighted
Community Support Team

Re: formula for converting year & month from text to make comparison

Hi @Arif270182,

```CY = CALCULATE(SUM(table4[Sales]),FILTER(table4,table4[Year]=YEAR(TODAY())))

LY = CALCULATE(SUM(table4[Sales]),FILTER(table4,table4[Year]=YEAR(TODAY())-1))```

Use a table visual to display data.

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User

Re: formula for converting year & month from text to make comparison

Not 100% on what you are going for here but perhaps this:

`Column = IF(RIGHT([Column1],4)="2017","LY","CY")`

Proud to be a Datanaut!

Highlighted
Community Support Team

Re: formula for converting year & month from text to make comparison

Hi @Arif270182,

```CY = CALCULATE(SUM(table4[Sales]),FILTER(table4,table4[Year]=YEAR(TODAY())))

LY = CALCULATE(SUM(table4[Sales]),FILTER(table4,table4[Year]=YEAR(TODAY())-1))```

Use a table visual to display data.

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Re: formula for converting year & month from text to make comparison

Hi

thanks for below update it worked but now i have another challange  in arranging month from Jan to Dec, I have already arranged table month column  month sequence not arranged

how can i fix this sequence