Forum Discussion
Cases MonthToDate
Hi, could anyone please help me modify the existing measure to show all cases per site as Month to Date? Currently, when I filter for April's month, I only see April's cases. However, I would like to see the cases for January, February, March, and April combined. Thanks in advance.
The measure:
MonthToDateCases measure =
CALCULATE(
SUM(Sheet1[Cases]),
FILTER(
Sheet1,
Sheet1[Date] <= MAX(Sheet1[Date]) &&
YEAR(Sheet1[Date]) = YEAR(MAX(Sheet1[Date]))
)
)
2 Replies
To modify your existing measure to show all cases per site as Month to Date, including the cases for January, February, March, and April combined when you filter for April's month, you can adjust the filter condition in your measure. Currently, your measure filters for the same year as the maximum date in your dataset. To make it show all cases from the start of the year up to the selected month, you need to adjust the filter to consider both the year and the month. Here's the modified measure:
MonthToDateCases measure =
CALCULATE(
SUM(Sheet1[Cases]),
FILTER( Sheet1,
Sheet1
[Date] <= MAX(Sheet1[Date]) &&
YEAR(Sheet1[Date]) = YEAR(MAX(Sheet1[Date])) &&
MONTH(Sheet1[Date]) <= MONTH(MAX(Sheet1[Date]))
)
)
With this modification, when you filter for April's month, you will see the cases for January, February, March, and April combined. This measure considers both the year and the month of the date to calculate the Month to Date cases.
