The goal is to highlight employee transactions vs the employee's varying schedule. The problem is that each employee has varying schedules (ex: some work daily / weekly / monthly ). I was successful at creating a measure that can reflect the employee's previous transactions but struggle reflecting dates in which there are no transactions in conjunction with their work schedule. Any advice on how I can reflect continuous date entries which corresponds with the employee's schedule?
Below you will find the following: Previous Transaction Measure / Measure for Continuous Dates / Sample Dimension Table / Sample Fact Table
Your support/advice is greatly appreciated.
[Previous Transaction Count] measure >> This works well as it will give me the previous transaction count but will skip any dates where there are no data
VAR _selDate = SELECTEDVALUE( 'Calendar'[Date] )
VAR _maxDate = CALCULATE(
LASTNONBLANK( 'Calendar'[Date],
CALCULATE( [Transaction_Count] )
)
, FILTER(
ALLSELECTED( 'Calendar' ), 'Calendar'[Date] < _selDate)
)
RETURN
IF(
NOT(
ISBLANK( [Transaction_Count] )
),
CALCULATE(
[Transaction_Count],
FILTER(
ALLSELECTED( 'Calendar' ),
'Calendar'[Date] = _maxDate
)
), BLANK()
)
Current Measure for Continuous Dates >> This works but does not reflect well for employees which does not have a daily schedule (ex: employees with weekly or monthly schedules will have 0 value dates for each individual day leading up to their next transaction). The goal is to try and have dates which correspond to their schedule even if there is no data (ex: if employee works weekly, the date output should follow a weekly cadance. Same for monthly).
IF(
[Previous Transaction Count] = BLANK(),
0,
[Previous Transaction Count]
)
Sample Dimension Table >> Notice column = Emp_Schedule (this will give the cadance in which date output should follow)
| Emp_id | Emp_Team | Emp_Schedule | Emp_Start_Date |
| 1 | Pizza | Weekly | Friday |
| 2 | Pizza | Weekly | Wednesday |
| 3 | Burgers | Daily | |
| 4 | Burgers | Monthly | 28 |
Sample Fact Table
| Emp_Team | Emp_Schedule | Trans_Count | Trans_Date | Previous_Value [Expected Output] | Explanation_For_Previous_Value |
| Pizza | Weekly | 10 | 8/3/2021 | 0 | |
| Pizza | Weekly | 5 | 8/10/2021 | 10 | |
| Pizza | Weekly | 0 | 8/20/2021 | 5 | |
| Pizza | Weekly | 0 | 8/27/2021 | 0 | Notice the week prior, there were no transaction. The goal is to reflect this week (date) if there are no transactions. |
| Pizza | Weekly | 15 | 9/3/2021 | 0 | Notice the week prior, there were no transaction. The goal is to reflect this week (date) if there are no transactions. |
| Pizza | Weekly | 2 | 8/2/2021 | 0 | |
| Pizza | Weekly | 0 | 8/11/2021 | 2 | |
| Burgers | Daily | 3 | 8/1/2021 | 0 | |
| Burgers | Daily | 12 | 8/2/2021 | 3 | |
| Burgers | Daily | 0 | 8/3/2021 | 12 | |
| Burgers | Daily | 0 | 8/4/2021 | 0 | Notice the day prior, there were no transaction. The goal is to reflect this day (date) if there are no transactions. |
| Burgers | Daily | 10 | 8/5/2021 | 0 | Notice the day prior, there were no transaction. The goal is to reflect this day (date) if there are no transactions. |
| Burgers | Monthly | 15 | 8/12/2021 | 0 | |
| Burgers | Monthly | 9 | 8/29/2021 | 15 | |
| Burgers | Monthly | 0 | 9/29/2021 | 9 | |
| Burgers | Monthly | 3 | 10/31/2021 | 0 | Notice the month prior, there were no transaction. The goal is to reflect this month (date) if there are no transactions. |
