topic Re: Creating a new column that returns the date 1st of march the year after operation date in DAX Commands and Tips

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

Anonymous — Thu, 02 Jul 2020 11:58:05 GMT

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

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

Anonymous — Thu, 02 Jul 2020 12:14:14 GMT

Hi Anonymous ,

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

Regards,

Harsh Nathani

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

Anonymous — Thu, 02 Jul 2020 12:24:04 GMT

Here you go Anonymous

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

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

Anonymous — Thu, 02 Jul 2020 13:41:11 GMT

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!

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

Anonymous — Thu, 02 Jul 2020 14:00:41 GMT

Thank you very much Anonymous!

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

Anonymous — Thu, 02 Jul 2020 14:24:14 GMT

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!

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

Thejeswar — Fri, 03 Jul 2020 05:09:30 GMT

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

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

Anonymous — Fri, 03 Jul 2020 07:00:18 GMT

Anonymous 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

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

Thejeswar — Fri, 03 Jul 2020 07:27:21 GMT

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) )

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

Anonymous — Fri, 03 Jul 2020 07:32:33 GMT

Hi Anonymous ,

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

Regrads

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

Anonymous — Fri, 03 Jul 2020 08:38:21 GMT

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):

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

Thejeswar — Fri, 03 Jul 2020 09:54:13 GMT

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!!!