Biweekly Dates Not working properly

[stoneluo]

Hallo, I am trying to merge a bunch of dates into bi weekly periods.

I used the following function:

Bi-Weekly Date = 
VAR _Date = 'Merged Data'[Timesheet Entry: Date]
VAR _WkNum = WEEKNUM ( _Date )
VAR _WkDay = WEEKDAY ( _Date ) - 1
VAR _SoY = STARTOFYEAR ( 'Merged Data'[Timesheet Entry: Date] )
VAR _WkStart = IF ( ISODD ( _WkNum ) , _Date - _WkDay, _Date - ( _WkDay + 7 ) )
RETURN 
MAX ( _SoY , _WkStart )

For instance, anything from Dec 12 - Dec 26, 2021 are in the same period.

However, when I deal with data that are new( in the sense that the 2 week period has not been completed yet), error starts to occur.

For some reason, data from Jan 3-Jan 8, 2022 gets into a separate period. They should be part of the Dec 26 - Jan 9 period, but in the image they don't do that. This error only happens at the end of the whole table, so I believe it's an issue with the code. It works properly for the data in the middle.(ie: dec 12 - dec26). May I ask why and how I can fix it? Thanks

[HotChilli]

Maybe you can clear up where the function comes from?

The algorithm may have worked for 2021 but doesn't work for 2022 (I don't know whether that is true). Another thing is that normally a function like this would be used on a calendar table i.e. not on a Fact table, this is important because some of these DAX calls don't work properly unless that table contains all dates.

I can explain why Jan 10 returns the value it does but I'm not going to wade in to fixing it because it would just be tinkering

-----

 Have a think about these things and tell me what you want to do

[stoneluo]

I figured that all I need to do was 

VAR _WkNum = WEEKNUM ( _Date )+1

I am pretty new to PBI and I am not to sure about the difference between fact table and calendar table. I never heard of the latter. Thanks for the first post anyways

[stoneluo]

Hi, thank you for the reply. I deleted the extra variable and it shows properly at the end now. but this triggered another problem. The bi weekly dates are in the periods given by the function, but the period is not the actual periods. They are off by 1 week.

Take an example of date Jan 10, 2022. It should belong to the period from Jan 2 - Jan 15, which means it should show Jan 4, but the function produces Jan 9, because it thinks Jan 9 - Jan 22 is a period. Technically, Jan 10 is within the range, so the function is working, but not producing desired output.

How can I correct this? I did some dumn moves like returning 

_WkStart - 7

and realized this would mess up the whole calculation. Please kindly share some more wisdom, thank you!

[HotChilli · Accepted solution]

Guessing that you didn't write the function yourself because there's an explicit test to see what's bigger, the start of the year or the week start.  It effectively starts a new period when it's a new year.

Remove the start of the year stuff for starters and see what that gets you.