topic Re: Date issue in DAX Commands and Tips

Date issue

Bergh — Fri, 20 May 2022 06:31:02 GMT

Hi. I have a date column that takes away 9 days. [date] -9, but I would not like to include Saturday and Sunday. Only working days / weekdays.

Re: Date issue

ValtteriN — Fri, 20 May 2022 06:55:59 GMT

Hi,

One way to do this is to add the following column in your calendar table:

Working days before today = IF(and(and(WEEKDAY('Calendar'[Date],2)<>6,WEEKDAY('Calendar'[Date],2)<>7),[date]<=TODAY()),1,0)

Then simply use these as a filter:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Date issue

tamerj1 — Fri, 20 May 2022 07:18:03 GMT

Hi Bergh
Not sure if your talking about a date table. However if you dont have a weekday column you can create one or just store in a variable

Working Day = VAR DayOfWeek = WEEKDAY( 'Date'[Date], 2) RETURN IF ( DayOfWeek In { 6, 7 }, "No", "Yes" )

You can then use this calculated column to filter your data

9 Days Ago = MINX ( TOPN ( 9, FILTER ( 'Date', 'Date'[Date] < EARLIER ( 'Date'[Date] ) && 'Date'[Working Day] = "Yes" ), 'Date'[Date], DESC ), 'Date'[Date] )

Re: Date issue

Bergh — Fri, 20 May 2022 08:33:05 GMT

Hi again, so there is no easy way like in Excel with =workdays[date]-9 ??

The columns look like this. If we look at ea_minus_25 2006-11-29. That was a Wednesday, so I if I take -9 it will should be 2006-11-17 because there are 2 weekends. So If I do a Date Table how do I fix this??

Re: Date issue

tamerj1 — Fri, 20 May 2022 09:47:25 GMT

Hi Bergh
There is no easy way

you can use the following code without the need for the date table

ea_minus_9 VAR Period = 9 VAR EndDate = TableName[ea] VAR StartDate = EndDate - 2 * Period VAR DatesPeriod = CALENDAR ( StartDate, EndDate ) VAR DatesAndWDs = ADDCOLUMNS ( DatesPeriod, "@WorkingDay", VAR DayOfWeek = WEEKDAY( [Date], 2) RETURN IF ( DayOfWeek In { 6, 7 }, "No", "Yes" ) ) VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" ) VAR TopNTable = TOPN ( Period + 1, WDsOnly, [Date], DESC ) RETURN MINX ( TopNTable, [Date] )

Re: Date issue

Bergh — Fri, 20 May 2022 13:13:00 GMT

I had some blank cells, then iI get a error. How do I do to fix this??

Re: Date issue

tamerj1 — Fri, 20 May 2022 13:19:51 GMT

ea_minus_9 = VAR Period = 9 VAR EndDate = TableName[ea] RETURN IF ( NOT ISBLANK ( EndDate ), VAR StartDate = EndDate - 2 * Period VAR DatesPeriod = CALENDAR ( StartDate, EndDate ) VAR DatesAndWDs = ADDCOLUMNS ( DatesPeriod, "@WorkingDay", VAR DayOfWeek = WEEKDAY ( [Date], 2 ) RETURN IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" ) ) VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" ) VAR TopNTable = TOPN ( Period + 1, WDsOnly, [Date], DESC ) RETURN MINX ( TopNTable, [Date] ) )

Re: Date issue

Bergh — Fri, 20 May 2022 13:24:00 GMT

Thanks! It works perfect!!

Re: Date issue

Bergh — Fri, 20 May 2022 13:42:26 GMT

If I want to Period = -6 Can I do that??

Re: Date issue

tamerj1 — Fri, 20 May 2022 14:05:07 GMT

Bergh
Not tested but I guess this should work both ways

ea_minus_9 = VAR Period = 9 VAR Date1 = TableName[ea] RETURN IF ( NOT ISBLANK ( EndDate ), VAR Date2 = Date1 - 2 * Period VAR DatesPeriod = CALENDAR ( MAX ( Date1, Date2 ), MAX ( Date1, Date2 ) ) VAR DatesAndWDs = ADDCOLUMNS ( DatesPeriod, "@WorkingDay", VAR DayOfWeek = WEEKDAY ( [Date], 2 ) RETURN IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" ) ) VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" ) RETURN IF ( Date1 > Date2, MINX ( TOPN ( Period + 1, WDsOnly, [Date], DESC ), [Date] ), MINX ( TOPN ( Period - 1, WDsOnly, [Date], ASC ), [Date] ) ) )

Re: Date issue

Bergh — Fri, 20 May 2022 14:27:35 GMT

No nothing happens. It is the same date 2022-05-20 become 2022-05-20

Re: Date issue

tamerj1 — Sat, 21 May 2022 08:28:17 GMT

Hi Bergh
This one is tested and confirmed working both ways. If you want to add dates you use N if youi want to minus days you use -N

ea_plus/minus_9 = VAR Period = -9 VAR Date1 = 'Date'[Date] RETURN IF ( NOT ISBLANK ( Date1 ), VAR Date2 = Date1 + 2 * Period VAR DatesPeriod = CALENDAR ( MIN ( Date1, Date2 ), MAX ( Date1, Date2 ) ) VAR DatesAndWDs = ADDCOLUMNS ( DatesPeriod, "@WorkingDay", VAR DayOfWeek = WEEKDAY ( [Date], 2 ) RETURN IF ( DayOfWeek IN { 6, 7 }, "No", "Yes" ) ) VAR WDsOnly = FILTER ( DatesAndWDs, [@WorkingDay] = "Yes" ) RETURN IF ( Date1 < Date2, MAXX ( TOPN ( ABS ( Period ) + 1, WDsOnly, [Date], ASC ), [Date] ), MINX ( TOPN ( ABS ( Period ) + 1, WDsOnly, [Date], DESC ), [Date] ) ) )

Re: Date issue

Bergh — Sat, 21 May 2022 09:00:45 GMT

Yes, now it works thanks!!