Forum Discussion
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 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.
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
6 Replies
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
I am really new at this. Can you supply me with the formula?
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
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
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!
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
