cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stoneluo
Regular Visitor

Biweekly Dates Not working properly

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

stoneluo_0-1643146595764.png

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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.

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

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

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
Regular Visitor

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
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!