Forum Discussion
Question re Creating a Measure based on a date "window"
I've been trying to create a measure that sums the instances of all leave dates within one year. I have a date table and a many to one relationship with the leave table. I have tried a couple of versions, but my current measure is as follows:
5 Replies
Walt1010 Try using
DAX
Leave 1Y Total =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
COUNT('LeaveData'[Absence Date]),
FILTER(
ALL('LeaveData'),
'LeaveData'[Absence Date] >= CurrentDate - 365 &&
'LeaveData'[Absence Date] <= CurrentDate
)
)Thanks for your replay. When I make those changes, the measure Leave 1Y Total in the visual
Name, 'Calendar'[Date], Leave 1Y Total, gives constant values such as 2550 for the dates where there is leave taken on consecutive days. So something isnt working. I tried using LeaveData'[Absence Date] in the visual, and I got slightly different values, but still incorrrect. So althout it looks right, something is messing it up!
When I remove the ALL function (used onALL('LeaveData')) the numbers come cack to the 1/2/3 range, which is what I would expect. Presumably the ALL function is removing the row context and providing all leave for all staff members. Removing the ALL therefore doesnt solve the real problem...
Hi Walt1010 ,
Your measure is not correctly accumulating leave instances within a rolling one-year window due to the way MAX('LeaveData'[Absence Date]) is being used. The key issue is that MAX('LeaveData'[Absence Date]) only considers the maximum date in the current filter context, which may not always reflect the desired rolling window. Instead, the measure should reference MAX('Calendar'[Date]), assuming the Calendar table is properly related to LeaveData. Using MAX('Calendar'[Date]) ensures that the measure dynamically evaluates based on the selected date in the report. Here’s the corrected version:
Leave 1Y Total = VAR CurrentDate = MAX('Calendar'[Date]) RETURN CALCULATE( COUNT('LeaveData'[Absence Date]), 'LeaveData'[Absence Date] >= CurrentDate - 365 && 'LeaveData'[Absence Date] <= CurrentDate )This ensures that the measure counts all leave instances that occurred in the past 365 days from the currently selected date in the calendar. However, using FILTER('LeaveData', ...) may not be the most efficient approach. Instead, DATESINPERIOD() can be used to dynamically evaluate a rolling one-year window:
Leave 1Y Total = CALCULATE( COUNT('LeaveData'[Absence Date]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -1, YEAR) )This approach ensures that the measure correctly sums leave occurrences within the last year, improving performance. When using this in a visual, make sure that Calendar[Date] is included in the axis or as a slicer to maintain correct filtering. If you are displaying this in a table with employee names, ensure that Calendar[Date] is also present in the visual to provide context for the measure. Let me know if you need further refinements!
Best regards,
Hi Walt1010 ,
The CurrentDate variable used in your metric should be based on the date in the date table, not the date in the vacation data table. This ensures that the metric is calculated correctly in the context of the date table. You can try the following code.
Leave 1Y Total = VAR CurrentDate = MAX('Calendar'[Date]) RETURN CALCULATE( COUNT('LeaveData'[Absence Date]), FILTER( 'LeaveData', 'LeaveData'[Absence Date] >= CurrentDate - 365 && 'LeaveData'[Absence Date] <= CurrentDate ) )Next, you could use the following fields in your report:
Employee Name, Absence Date, Leave 1Y TotalorEmployee Name, Calendar[Date], Leave 1Y TotalThis ensures that the metric is calculated correctly in the context of the date table and avoids cumulative results.
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file. Alternatively, share the download link of the PBI file.
