Previous workday from calender table

[jessicaritch]

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 table column would be "Table_ExternalData_1[Job]", this is the column that lists all jobs.  The calendar table is named "dcalendar".  Inside the dcalendar table, I created a column named "workday" of course this field is using numbers 1 - 8.  I need a dax formula that will give me the amount of jobs from the previous workday. 

[idrabble]

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]

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!

[v-shex-msft]

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

[v-shex-msft]

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]

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

[v-shex-msft · Accepted solution]

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