Forum Discussion

aramirez2's avatar
aramirez2
Icon for Helper I rankHelper I
5 years ago
Solved

Previous working day column

Hi!

 

I need to create a previous working day column in my calendar table which has the following structure:

 

 

WorkingDay column = "1" means that we opened our store and I need for each Date value which was the previous day we opened the store. However I am not able to reach my goal because:

 

PrevWorkingDay column works properly from Tuesday to Saturday.
 
PrevWorkingDay2 column works properly for the last week.
 
PrevWorkingDay3 only shows up the last working day.
 
May anyone help me please?
 
Thanks in advance.
 
Regards.
Need Help
  • aramirez2's avatar
    aramirez2
    5 years ago

    Thanks amitchandak  but your Work Date Cont column gives today date (Monday) for today record as Previous Work Day. 

     

    I got the solution. Now PrevWorkDay column gives Friday as Previous Work Day for Saturday, Sunday and Monday records.

     

     

     

     

    PreviousWorkDay = 
var a = Hoja1[Date]
RETURN CALCULATE(MAX(Hoja1[Date]); FILTER(ALL(Hoja1); Hoja1[Date] < a && Hoja1[WorkingDay] = 1))

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    aramirez2 , You need few columns in date table

     

    Work Day = if(WEEKDAY([Date],2)>=6,0,1)
    Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
    Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
    Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
    Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])
    Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

     

    measures

    This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
    Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))
    diff =[This Day] - [Last work day]

     

     

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

    The measures like

    • aramirez2's avatar
      aramirez2
      Icon for Helper I rankHelper I
      5 years ago

      Hi amitchandak

       

      Thanks for your answer. However your formulas does not seem to show the desired result:

       

       

       

      I have already created Workday column with "1" and "0" values. This data is correct and PrevWorkDay is nearly to work properly. I would like to fill up Blank PrevWorkDay cells with the very last work day.

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        aramirez2 , I think, in the blog I shared I have updated nonworking dates with the last working dates 

         

        Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])