topic Re: Adding last 3 days when day is Monday in DAX Commands and Tips

Adding last 3 days when day is Monday

Anonymous — Mon, 26 Apr 2021 16:10:29 GMT

Hi Al ,

I have a Yesterday SHipment measure as follows:

Yesterday = CALCULATE(SUM('Table'[Tons]), FILTER('Table','Table'[Date]=TODAY()-1))

But Now I want to add a logic to it that ,

"if today is Monday then add the shipment of Friday +Saturday + Sunday as previuos day shipment; else previous day shipment"

Date	Tons	Yesterday Shipment
Thursday, April 1, 2021	100	0
Friday, April 2, 2021	150	100
Saturday, April 3, 2021	200	150
Sunday, April 4, 2021	250	200
Monday, April 5, 2021	300	600 (= 150+200+250)
Tuesday, April 6, 2021	350	300
Wednesday, April 7, 2021	400	350
Thursday, April 8, 2021	450	400
Friday, April 9, 2021	500	450
Saturday, April 10, 2021	550	500
Sunday, April 11, 2021	600	550
Monday, April 12, 2021	650	1650 (= 500+550+600)
Tuesday, April 13, 2021	700	650
Wednesday, April 14, 2021	750	700
Thursday, April 15, 2021	800	750
Friday, April 16, 2021	850	800
Saturday, April 17, 2021	900	850
Sunday, April 18, 2021	950	900
Monday, April 19, 2021	1000	2700
Tuesday, April 20, 2021	1050	1000
Wednesday, April 21, 2021	1100	1050
Thursday, April 22, 2021	1150	1100
Friday, April 23, 2021	1200	1150
Saturday, April 24, 2021	1250	1200
Sunday, April 25, 2021	1300	1250
Monday, April 26, 2021	1350	3750

Re: Adding last 3 days when day is Monday

edhans — Mon, 26 Apr 2021 17:32:46 GMT

See if this works for you Anonymous

I used a date table here. Made it really simple to walk back the days.

This is the measure I used:

Yesterday's Tons = VAR varWeekDay = MAX('Date'[Day Name]) VAR varCurrentDay = MAX('Date'[Date]) VAR varPreviousDayTons = CALCULATE( [Total Tons], 'Date'[Date] = varCurrentDay - 1 ) VAR varWeekendTons = CALCULATE( [Total Tons], DATESBETWEEN( 'Date'[Date], varCurrentDay - 3, varCurrentDay -1 ) ) VAR Result = IF( varWeekDay = "Monday", varWeekendTons, varPreviousDayTons ) RETURN Result

It has the definition for both weekend tons (Fri-Sun) and regular tons (just yesterday) but it will only calculate the one it needs based on if the weekday is Monday - which comes from the date table.

Here is my PBIX file if you want to see how I set it all up. You'll notice used a filter in CALCULATE rather than filtering the FACT table based on the date. Generally considered good practice. Filtering an entire table shoudl be avoided. The larger the model, the worse the performance impact becomes.

Re: Adding last 3 days when day is Monday

Fowmy — Mon, 26 Apr 2021 17:50:22 GMT

Anonymous

If you want to add a column to your table then add a column with this code. Hope you have data for all days.

Yesterday Tons = VAR __DATE = Table1[Date] RETURN IF( WEEKDAY([Date]) = 2, CALCULATE(SUM(Table1[Tons]), Table1[Date] < __DATE && Table1[Date] >= __DATE-3 , ALL(Table1)), CALCULATE(SUM(Table1[Tons]), Table1[Date] = __DATE -1, ALL(Table1)) )

Re: Adding last 3 days when day is Monday

CNENFRNL — Mon, 26 Apr 2021 19:08:54 GMT

All 3 solution by DAX formula, calculated column and PQ.

Yesterday_DAX = VAR __dt = MAX( SHIPMENT[Date] ) RETURN IF( WEEKDAY( __dt, 2 ) = 1, CALCULATE( [total], DATESINPERIOD( SHIPMENT[Date], __dt - 1, -3, DAY ) ), CALCULATE( [total], DATEADD( SHIPMENT[Date], -1, DAY ) ) )

Yesterday_CC = VAR __dt = SHIPMENT[Date] RETURN IF( WEEKDAY( SHIPMENT[Date], 2 ) = 1, SUMX( FILTER( SHIPMENT, __dt - 3 <= SHIPMENT[Date] && SHIPMENT[Date] <= __dt - 1 ), SHIPMENT[Tons] ), SUMX( FILTER( SHIPMENT, SHIPMENT[Date] = __dt - 1 ), SHIPMENT[Tons] ) )

PQ solution, unnecessarily overcomplicated, but just for fun,

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZIxD4IwFIT/CunM0HtQoKOLmxMkDoSBBBJJjBq0g//eGLHvaV7nu156972+N90prPdpfObZ7rYu5wx5RpZgcgNrzZD3Zr8uQifW3Udvx0dYhaOIDtoS2nAResn6lnC4St1Fvdjed2GWX6zYsAUc5+nyY6mjpfxm/PVs2OG0nj7qzuo9YdnitKLgLSurNQWPWTm1KnjM2ia6ggetnV4WvGmjUgVv2iSwgjf1KlfwpF4FCy9vS+1LVlhScEncKBJ4SdwpVMDE04ISiKkUHpUx8bIoVMhUCcc7Y3gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Tons = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Tons", Int64.Type}}), Custom1 = Table.AddColumn( #"Changed Type", "Yesterday_PQ", let l=Table.ToColumns(#"Changed Type"), rs=Record.FromList(l{1}, List.Transform(l{0}, each Text.From(Number.From(_)))) in each if Date.DayOfWeek([Date])=1 then List.Accumulate({Number.From([Date])-3 .. Number.From([Date])-1}, 0, (s,c) => s+Record.FieldOrDefault(rs, Text.From(c), 0)) else Record.FieldOrDefault(rs, Text.From(Number.From([Date]-#duration(1,0,0,0)))) ) in Custom1

Re: Adding last 3 days when day is Monday

Anonymous — Mon, 26 Apr 2021 21:03:27 GMT

Can you share the logic for "Day name " and using MAX for it.

For e.g. For today this expresssion shows Wednesday. Should it be showing "Monday". In my actual report it did not add Fri+SAt+Sun for today.

This Is how I used in my report:

Re: Adding last 3 days when day is Monday

edhans — Mon, 26 Apr 2021 21:49:04 GMT

Anonymous the day name is a field in my date table - which was in the PBIX file I used. MAX() is just a way to turn the currently visible day name into a scalar value that I can use in a filter later.

Is that what you are asking?

Re: Adding last 3 days when day is Monday

Anonymous — Tue, 27 Apr 2021 15:34:07 GMT

Yes , Thats what my question was .

However, I was wondering in what case will "Var Weekday" will give the result as 'Monday' which will then kick-in the "If (var Weekday = "monday") part of Dax. Hope I am understanding it correct.

Re: Adding last 3 days when day is Monday

edhans — Tue, 27 Apr 2021 16:40:57 GMT

varWeekday will return "Monday" when the visible date is Monday. It isn't based on the system date - not TODAY().

Is that what you are asking?

So if you load data and it says Jan 19, 2015 for a given record, the measure I gave you will go "Oh, that is a monday, so do 3 days of totals". If the next row is Jan 20, 2015, it will go "Nope, this is Tuesday, so just look back one day."

Re: Adding last 3 days when day is Monday

Anonymous — Wed, 28 Apr 2021 20:55:18 GMT

HI edhans . Thanks for your response.

I think I did not ask this question on this forum in correct context. Apologies for that.

Basically, I plan to use this query as part of a table visual . Please see below the visual . Here 'yesterday tons' right now is simply :

Yesterday Tons= CALCULATE(SUM('Shipment Detailed'[Shipped quantity (tons)]),FILTER('Shipment Detailed','Shipment Detailed'[Ship date]=TODAY()-1))

Please note that all the feilds here are from same table "Shipment Detailed" . I also have a date table built in the model but did not use it here as did not feel the need.

Now , I did use your suggestion and modified this query as below , but its giving me the same result as older one.

Yesterday Tons _Modified=

VAR varWeekDay = MAX('Shipment Detailed'[Day Name])

VAR varCurrentDay = MAX('Shipment Detailed'[Ship date])

VAR varPreviuosDayTons= CALCULATE(SUM('Shipment Detailed'[Shipped quantity (tons)]),'Shipment Detailed'[Ship date] = varCurrentDay - 1)

VAR varWeekendTons = CALCULATE(SUM('Shipment Detailed'[Shipped quantity (tons)]), DATESBETWEEN('Shipment Detailed'[Ship date], varCurrentDay - 3, varCurrentDay - 1))

VAR Result = IF(varWeekDay="Monday", varWeekendTons,varPreviuosDayTons)

return Result

I am not sure what is it that I am doing incorrect .

I have uploaded a sample data in excel from this table (basically only the columns which are used here) one-drive. Here is the link https://1drv.ms/x/s!AoEAHRm368Nsbf4XFy6h4yJ4J9s?e=QeTq1d

Please let me know what you think about it.