count of weekdays passed

[jwi1]

Good day,

I have a question about the days in a week.

as an example: today is week 36, thursday, 3/9/2020.

i want a formula which gives me the following outcome:

total days passed (excluding thursday): 3 (monday, tuesday, wednesday)

Thanks!

John

[v-alq-msft]

Hi, @jwi1 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column or a measure as below.

Calculated column:

Weekdays Column = 
var _weekday = WEEKDAY([Date])
return
IF(
    _weekday>=2&&_weekday<=6,
    _weekday-2,
    5
)

Measure:

Weekdays Measure = 
var _weekday = WEEKDAY(SELECTEDVALUE('Table'[Date]))
return
IF(
    _weekday>=2&&_weekday<=6,
    _weekday-2,
    5
)

Result:

If you want to get the result based on today, you may try the following measure. Today is 9/7/2020 (Monday).

Count of weekdays passed based on today = 
var _weekday = WEEKDAY(TODAY())
return
IF(
    _weekday>=2&&_weekday<=6,
    _weekday-2,
    5
)

Result:

Best Regards

Allan

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

[jwi1]

Hi @allan 

Thanks a lot for your clear explanation.

If we look at this week (37), the amount of working days passed = 0 (monday is the first working day).

However, in week 36, 5 working days are passed.

Is it possible to have a measure which gives me the following outcome:

- week 35: 5

- week 36: 5 

- week 37: 0

Thanks!

John

[Greg_Deckler]

@jwi1 - Perhaps:

Measure =
  VAR __Weeknum = MAX('Table'[Weeknum])
  VAR __CurrentWeeknum = WEEKNUM(TODAY())
  VAR __Weekday = WEEKDAY(TODAY(),2)
  VAR __Calendar = FILTER(ADDCOLUMNS(CALENDAR(TODAY()-10,TODAY()+10),"Weeknum",WEEKNUM([Date]),"Weekday",WEEKDAY([Date])),[Weeknum]=__CurrentWeeknum && [Weekday]<6)
RETURN
  IF(__Weeknum < __CurrentWeeknum,5,COUNTROWS(FILTER(__Calendar,[Weekday]<=__Weekday)))

[Greg_Deckler · Accepted solution]

@jwi1 - This seems like a variation of Net Work Days. You could adapt that approach to exclude specific days. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

Might also need Week Starting:https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487#M123

Or, you could just do this:

Week Days Passed =
  VAR __Today = TODAY()
  VAR __Calendar = ADDCOLUMNS(CALENDAR(TODAY()-8),TODAY()),"WeekNum",WEEKNUM([Date],2))
RETURN
  COUNTROWS(FILTER(__Calendar,[WeekNum]=WEEKNUM(TODAY(),2) && WEEKDAY([Date],2)<WEEKDAY(TODAY(),2))
  

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@jwi1 , Try

WEEKDAY(today(),2)-1

Days passed = WEEKDAY('Date'[Date],2)-1

measure

WEEKDAY(max('Date'[Date]),2)-1

This should also work

WEEKDAY(today(),3)

If you are looking for Week till date calc, refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...