Start of Week Blank?

[Anonymous]

Hi,

I have created a report displaying initiated dates weekly since 2018

This report has worked perfectly up until April 30th and I am not sure why.

The column "init" is the init date in the system, the "testtt" column is how I am able to create the same weekly incraments for all 3 years, and the "Init Start of Week test" column is the weeks I want to display.

As you can see, on April 30th, the "Init Start of Week test" column is blank.... it continues to be blank up until the current date.

Below are the formulas I used to get each column...

testtt = if(and(and(AND(Query1[InitYear]="2018",Query1[init month]=1),Query1[init day]>6),Query1[init day]<8),Query1[INIT DATE]-1, if(and(and(Query1[InitYear]="2018",Query1[init month]=12),Query1[init day]=31),Query1[INIT DATE]-2,if(and(and(Query1[InitYear]="2018",Query1[init month]=1),Query1[init day]>15),Query1[INIT DATE]-1,if(and(Query1[InitYear]="2018",Query1[init month]>=2),Query1[INIT DATE]-1,if(and(and(Query1[InitYear]="2019",Query1[init month]=12),Query1[init day]=31),Query1[INIT DATE]-3,if(and(and(Query1[InitYear]="2019",Query1[init month]=1),Query1[init day]>=6),Query1[INIT DATE]-2,if(and(and(Query1[InitYear]="2019",Query1[init month]=1),Query1[init day]>15),Query1[INIT DATE]-2,if(and(Query1[InitYear]="2019",Query1[init month]>=2),Query1[INIT DATE]-2,if(and(and(Query1[InitYear]="2020",Query1[init month]=12),Query1[init day]=31),Query1[INIT DATE]-4,if(and(and(Query1[InitYear]="2020",Query1[init month]=1),Query1[init day]>=6),Query1[INIT DATE]-3,if(and(and(Query1[InitYear]="2020",Query1[init month]=1),Query1[init day]>15),Query1[INIT DATE]-3,if(and(Query1[InitYear]="2020",Query1[init month]=2),Query1[INIT DATE]-3,if(and(AND(Query1[InitYear]="2020",Query1[init month]=3),Query1[init day]<5),Query1[INIT DATE]-4,if(and(AND(Query1[InitYear]="2020",Query1[init month]>=3),Query1[init day]>=5),Query1[INIT DATE]-4,if(and(AND(Query1[InitYear]="2020",Query1[init month]=4),Query1[init day]<2),Query1[INIT DATE]-4,Query1[INIT DATE])))))))))))))))

Init Start of Week test = DATEADD(Query1[testtt],-1*WEEKDAY(Query1[testtt])+weekday(STARTOFYEAR(Query1[testtt])),DAY)

Please let me know if my question does not make sense...

Thank you!
Sarah

[V-pazhen-msft]

@Anonymous 

It is really overly long and messy, first of all I would suggest to change your formula to Switch(true() for multiple if function, and use && instead of And().

https://docs.microsoft.com/en-us/dax/switch-function-dax

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

[Anonymous]

attached is a screenshot

[amitchandak]

@Anonymous , please see if you can use

Monday to Sunday week

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

Sunday to Saturday Week

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)

[Anonymous]

@amitchandak  this doesnt work bc for example, monday-sunday of week may 4 2020-may 10 2020 is different than last year, as may 4th is a saturday in 2019 instead of a monday in 2020 😞

[amitchandak]

@Anonymous , Not sure I got you. If you always need Sunday to Saturday week, this should work for all your date in the calendar.

Can explain the output you need

[Anonymous]

hm,  @amitchandak 

I do need monday-sunday calendar weeks, but only for 2020. for 2019 and 2018, I want the same dates in the week as 2020, so in the previous example i would want the week of May 4 2020 - May 10 2020, but for 2019, I would want the same week, May 4 2019 - May 10 2019

that is why my DAX formula is so long... to accomidate the fact that may 4 2019 is on a Saturday, not a monday, but is included in the week May 4 - May 10

Hope this makes a little bit more sense... I am not the best at explaining things...

Thank you!
Sarah

[amitchandak]

Try LIke. Quick Calc. Do modification as per need

Var _Date =
switch( True ,
[year] =2019 , date(year([Date])+1,month([Date]),Day([Date]))
[year] =2018 , date(year([Date])+2,month([Date]),Day([Date]))
)
var _wt = _Date+-1*WEEKDAY(_Date,2)+1
return
switch( True ,
[year] =2019 , date(year(_wt)-1,month(_wt),Day(_wt))
[year] =2018 , date(year(_wt)-2,month(_wt),Day(_wt))
)

[Anonymous]

not sure i understand... i just dont understand why my formula stops outputting start of week dates after 4/30/2020... it works perfectly with my other dates before that date

[mahoneypat]

Your DAX expression appears to be overly long and complex, and there is likely a more efficient way to reach your goal.  Can you post an example of your full starting table and your desired output and the community can likely propose a different approach?

Regards,

Pat

[Anonymous]

@mahoneypat 

https://docs.google.com/spreadsheets/d/15uDsBpI1gwDsppWgsTeQG0XmWLTuS6Sx/edit#gid=1402235018

here is a sample spreadsheet

I just got rid of duplicate "INIT DATE"s... My desired output would be the "init start of week official" column, but from there i want the "Init Week" column so i can use it as a filter for all 3 years, if this makes sense

Thank you!
Sarah