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

idrabble's avatar
idrabble
Frequent Visitor
9 years ago

If your calendar table does not include weekends or holidays then I would suggest a new calculated column in the calendar table with the following DAX:

LastWeekDay = 'dcalendar'[CalendarDate]-1 

This is where [CalendarDate] is the unique value for each day

 

Then create a measure thus:

Last Work Date = LOOKUPVALUE('dcalendar'[LastWeekDay],'dcalendar'[CalendarDate],today())

 

Then you can create your final measure:

Count of Last Work Date Jobs = calculate(COUNT(Table_ExternalData_1[Job]),filter(Table_ExternalData_1,Table_ExternalData_1[JobDate]=[Last Work Date]))

 

This final measure can then be placed in a Card to show the number of jobs from yesterday

 

Let us all know how you get on

 

Ian

jessicaritch's avatar
jessicaritch
Regular Visitor
9 years ago

This is great!  U got it to work.

 

Now, I need the formula for

 

1. Last 7 days

2. Last 30 days

3. Last 60 days

4. Last 90 days

5.  1 year

 

Once I have this dashboard created for these metrics then all of our reports will follow this setup.

 

It is greatly appreciated!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi jessicaritch,

     

    You can direct use calculated column with dateadd function to achieve your requirement:

    Previous Week = DATEADD('Calendar'[Date],-7,DAY)
    Previous Month = DATEADD('Calendar'[Date],-1,MONTH)
    Previous Quarter = DATEADD('Calendar'[Date],-1,QUARTER)
    Previous Year = DATEADD('Calendar'[Date],-1,Year)

     

    Regards,

    Xiaoxin Sheng