Workingday Number fails when refresh in service

[Anonymous]

Hi all, 

I have a datetable that among other things calculates the workday Number for each month: 

Works great in desktop, but when I refresh in service it counts each day as two instead of one. For example, September should look like: 

But after refresh: 

The formula for Workday number is:

Workday Number =

VAR CurrentMonth = Dates[Period]

VAR Monthtable = FILTER(ALL( Dates ), Dates[Day type 2] = "Workday" && Dates[Period] = CurrentMonth )

RETURN

IF(Dates[Day type 2] = "Weekend",

BLANK(),

RANKX( MonthTable, CALCULATE( AVERAGE( Dates[DayofMonth] ) ), , ASC ))

I'm really stumped. If anyone has an idea of what I could have done wrong please come with an suggestion. I would be eternally grateful. 

[Greg_Deckler · Accepted solution]

@Anonymous - What if you try this:

Workday Number =
VAR CurrentMonth = Dates[Period]
VAR Monthtable = 
  FILTER(
    ALL( Dates ), 
    Dates[Day type 2] = "Workday" && Dates[Period] = CurrentMonth && [Date]<=EARLIER([Date])
  )
RETURN
  COUNTROWS(Monthtable)

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you please try to use the following calculated column, it works well based on my test:

WorkDay Number = IF('Table'[Day type 2] = "Weekend",BLANK(),RANKX(FILTER('Table','Table'[Period] = EARLIER('Table'[Period])&&'Table'[Day type 2] = "WorkDay"),'Table'[Date],,ASC))

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

Best Regards,

Dedmon Dai

[Greg_Deckler · Accepted solution]

@Anonymous - What if you try this:

Workday Number =
VAR CurrentMonth = Dates[Period]
VAR Monthtable = 
  FILTER(
    ALL( Dates ), 
    Dates[Day type 2] = "Workday" && Dates[Period] = CurrentMonth && [Date]<=EARLIER([Date])
  )
RETURN
  COUNTROWS(Monthtable)

[Greg_Deckler]

@Anonymous Wow, that is super bizarre, not sure I have ever seen that. And not sure how to troubleshoot either. I'll try to take a deeper look at the DAX code. This is import mode I take it. 

If all else fails. You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".