Hello,
I have a calendar table and below table which shows employee ID, date, remaining vacation in hours and which year it is.
Date id remaining_vacation Year_Week
01-Jul-23 104_0 147.75 2023_26
07-Jul-23 104_0 140.25 2023_27
10-Jul-23 104_0 132.75 2023_28
11-Jul-23 104_0 125.25 2023_28
12-Jul-23 104_0 117.75 2023_28
13-Jul-23 104_0 110.25 2023_28
14-Jul-23 104_0 102.75 2023_28
18-Jul-23 104_0 95.25 2023_29
01-Aug-23 104_0 95.25 2023_31
07-Aug-23 104_0 87.75 2023_32
I want to create below matrix visual
WK26 WK27 WK28 WK29 WK30 WK31
Employee ID - 104 147.75 140.25 102.75 95.25 95.25 95.25
Employee ID - 105 90 90 45 45 45 45
…
I have a Calendar table connected to the Date.
In Matrix visual I use Year_Week from Calendar table. It is not required though.
So i want to write a measure which will Current Date and Last Available Date and will show number based on result in the filed. Something similar like below:
field_param_zustatek_dovolene =
VAR cur_date = SELECTEDVALUE('calendar'[YearWkNo])
VAR max_date = MAXX(
FILTER(ALL('calendar'),'calendar'[YearWkNo]< cur_date),
'calendar'[YearWkNo]
)
VAR _val2 =
CALCULATE (
MAX( ee_vacation_balance[zustatek_dovolene]),
FILTER( ALL('calendar'),'calendar'[YearWkNo] = max_date)
)
VAR _val =
CALCULATE(
MIN(ee_vacation_balance[zustatek_dovolene]),
FILTER( ALL('calendar'),'calendar'[YearWkNo] = cur_date)
)
RETURN
IF ( ISBLANK( _val),_val2,_val)
But the solution above is deficient because it shows only the last week, if there are 3 weeks empty, it will only populate the previous week, not the other which are missing.
I also tried below. Which calculates correctly populated previous date for every bucket, but it does not show entries for blank values.
Can somebody help and point me at right direction?
