Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |