Weeknum date range not including current day

[Anonymous]

Hello,

I am creating a report for our payroll department that will display timesheet information by employee and by day (for the current pay period). This dataset is tied to a database which is essentially live (as time is entered each day the timesheet info goes directly into the database). We would like this report to be used by our payroll department to see if there is any time that is missing before we run the time through our payroll system. 

To achieve this, I am using the WeekNum DAX function to display only the information for the current pay period. The problem I am running into is that the information displayed does not include any information for the current day (even when the current day is a part of the weeknum). This becomes problematic on the days when timesheets are due, because we can't see the changes as they are made.

Below I've added a couple of screenshots displaying the problem:

Weeknumber two shown below has the correct start day, but is stopping at yesterday's date (1/10/2022) instead of including today's date (1/11/2022).  I know there is data in the database for today, but it won't display. 

I am using the following DAX code to get my weeknum: 

WeekNumber = WEEKNUM(TimeCardEntry[date],13)

Any help would be greatly appreciated!

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

I create a sample and use your formula , Date range looks normal .

(1)I create a calendar date table from 2022-01-01 to 2022-12-31.

Table = CALENDAR(DATE(2022,01,01),DATE(2022,12,31))

(2)Use your formula to create the week number.

WeekNumber = WEEKNUM('Table'[date],13)

(3)Create a calculated column to return the date range .

Date range =
var _mixdate=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[WeekNumber]))
var _maxdate=CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[WeekNumber]))
return
FORMAT('Table'[Date],_mixdate & "-" & _maxdate)

The final result is as shown :

You can check your data through my sample . I have attached my pbix file.

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your help! 

[amitchandak]

@Anonymous , If you are looking for week end date 

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