Forum Discussion

jessicaritch's avatar
jessicaritch
Regular Visitor
9 years ago
Solved

Previous workday from calender table

I have my data table and then my calendar table.  They are linked in relationships.  I need the preivous day data.  This data does not included weekends or holidays so that's not a big deal.  My main...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi jessicaritch,

     

    I found a better function which simple than above, perhaps you can take look at below sample.

    Logic: find the last date which has the similar weekday.

     

    Create a calculate table with base fileds:

    Table = ADDCOLUMNS(CALENDAR(DATE(2015,1,1),TODAY()),"WeekNum",WEEKNUM([Date],1),"DayOfWeek",WEEKDAY([Date],1),"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"),"WeekDay",FORMAT([Date],"dddd"))

     

     

    Calculate column:
    Previous WeekDay = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Date]<EARLIER([Date])&&[DayOfWeek]=EARLIER('Table'[DayOfWeek])))

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi jessicaritch,

 

In my opinion, you can use "weeknum" with "weekday" to mark the unique "weekday", then you can use current "weekday" and current "weeknum" -1 to find out the previous weekday.

 

Regards,

Xiaoxin sheng

jessicaritch's avatar
jessicaritch
Regular Visitor
9 years ago

I am really new at this. Can you supply me with the formula?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi jessicaritch,

     

    I found a better function which simple than above, perhaps you can take look at below sample.

    Logic: find the last date which has the similar weekday.

     

    Create a calculate table with base fileds:

    Table = ADDCOLUMNS(CALENDAR(DATE(2015,1,1),TODAY()),"WeekNum",WEEKNUM([Date],1),"DayOfWeek",WEEKDAY([Date],1),"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"),"WeekDay",FORMAT([Date],"dddd"))

     

     

    Calculate column:
    Previous WeekDay = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Date]<EARLIER([Date])&&[DayOfWeek]=EARLIER('Table'[DayOfWeek])))

     

     

    Regards,

    Xiaoxin Sheng