Creating a new column that returns the date 1st of march the year after operation date

[Anonymous]

I have a table-column that includes the operation date (first day of operation) per machine. I am doing analysis on the transactions on these machines and they are affected by seasonality. Therefore I would like to add a column to my data that returns the first date of the "high-season" where the machine is fully operational.

I would like the column to return the 1st of March the year after the first operation date for every machine if the operation date is after the 1st of March. If the operation date is before the first of March in its respective year it should return the first of March the same year as operation date. See example below:

Operation date = 04.05.17 --> 01.03.18

Operation date = 14.02.19 --> 01.03.19

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a Calculated Column

Ops Day Seasonality = 

var _year = Year('Table'[Operation date])
var _month = Month ('Table'[Operation date])
var _day = Day('Table'[Operation date])

RETURN

SWITCH(
    TRUE(),
    _month = 3 && _day = 1,'Table'[Operation date],
    _month = 1 || _month = 2   ,Date (_year, 3,1),
    Date (_year+1, 3,1)
    )
    

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[harshnathani]

Hi @Anonymous ,

Please share some sample data and the expected output to help you.

Regards,

Harsh Nathani

[Anonymous]

Here you go @harshnathani

I want the new column to be returned in the same way as "Operation date seasonality" based on the data in "Operation date".

So for every date in "Operation date" after 1st of March in a specific year, "Operation date seasonality" should return 1st of March the next year and if the date in "Operation date" is on or before 1st of March in a year "Operation date seasonality" should return 1st of March the same year.

Operation date	Operation date seasonality
03.04.17	01.03.18
12.01.20	01.03.20
15.10.16	01.03.17
16.09.18	01.03.19
18.02.16	01.03.16
01.03.19	01.03.19
02.03.17	01.03.18
17.01.17	01.03.17
29.12.15	01.03.16

[Thejeswar]

Hi @Anonymous ,

A much simpler solution would be to use the below DAX with concatenate function

Column = CONCATENATE("01",CONCATENATE(".03.",IF(MONTH('Table'[Date]) <= 3, YEAR('Table'[Date]), YEAR('Table'[Date])+1)))

Regards,

Thejeswar

[harshnathani · Accepted solution]

Hi @Anonymous ,

Create a Calculated Column

Ops Day Seasonality = 

var _year = Year('Table'[Operation date])
var _month = Month ('Table'[Operation date])
var _day = Day('Table'[Operation date])

RETURN

SWITCH(
    TRUE(),
    _month = 3 && _day = 1,'Table'[Operation date],
    _month = 1 || _month = 2   ,Date (_year, 3,1),
    Date (_year+1, 3,1)
    )
    

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[Anonymous]

Thank you very much @harshnathani!

[harshnathani]

HI @Anonymous ,

If the solution works , pls mark it as Solution.

This will help others in the community.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[Anonymous]

@harshnathani One more question. I would like to add something to the existing code.

I would like the column to return the year we are currently in (2020) if the operation date is after 01.03.20. By this I mean if the operation date of new machines this year is 02.02.20 it should return 01.03.20, but if the operation date of a new machine is 16.03.20 it should return 16.03.20 and not 01.03.21 like it does now (otherwise transactions on all machines with an operation date after 01.03.20 is left out).

In example like this:

Operation date	Operation date seasonality
02.02.20	01.03.20
15.05.20	15.05.20
12.03.20	12.03.20
17.01.20	01.03.20

[harshnathani]

Hi @Anonymous ,

The solution provided by @Thejeswar  will work in this case then.

Regrads

HN

[Thejeswar]

Hi @Anonymous ,

Modify the Calculated Column you created as follows

Just modify as _day >= 1

Ops Day Seasonality = 

var _year = Year('Table'[Operation date])
var _month = Month ('Table'[Operation date])
var _day = Day('Table'[Operation date])

RETURN

SWITCH(
    TRUE(),
    _month = 3 && _day >= 1,'Table'[Operation date],
    _month = 1 || _month = 2   ,Date (_year, 3,1),
    Date (_year+1, 3,1)
    )

[Anonymous]

@Thejeswar  I have tried to do this and it still does not work. For example I have an operation date of 29.05.20 in my dataset and this returns 01.03.20 in the new column and not 29.05.20. 

Do you have an idea of where the problem is?

My coding looks like this (exactly like yours):

[Thejeswar · Accepted solution]

Hi @Anonymous ,

This thing not working for 29.05.20 is an expected scenario.

The Script is written such that if the data is of month 3 and an data, it will show the same data,

if the data is from months 1 and 2, it will show 1.3.(that year)

for other dates it will show 1.3.nextyear

So for 29.05.20 it is showing 01.3.21

Use the below modified script. The Below script returns the data as it is if it is greater that 1-Mar of the current year and for all prior dates as you wanted. The Output also given below.

Ops Day Seasonality = 
var _curryear = YEAR(TODAY())
var _year = Year('Table'[Operation date])
var _month = Month ('Table'[Operation date])
var _day = Day('Table'[Operation date])

RETURN
IF('Table'[Operation Date] >= DATE(YEAR(TODAY()),  03, 01), 'Table'[Operation Date],
SWITCH(
    TRUE(),
    _month = 1 || _month = 2   ,Date (_year, 3,1),
    Date (_year+1, 3,1)
    )
    )

If this answers your quetion, accept as soluion and give a kudos!!!