I have a report that calculates year-over-year KPIs, from the first of the year to the current date in the respective year. The end customer now wants a table visual that shows a history of these weekly KPI summaries.
example KPI table data:
| Saturday's Date | Receipts this year | Receipts this year (cumulative) | Receipts last year | Receipts last year (cumulative) |
|---|---|---|---|---|
| 01/04/2025 | 4 | 4 | 5 | 5 |
| 01/11/2025 | 25 | 29 | 17 | 22 |
| 01/18/2025 | 9 | 38 | 9 | 31 |
| 01/25/2025 | 14 | 52 | 8 | 39 |
| 02/01/2025 | 41 | 93 | 27 | 66 |
| 02/08/2025 | 12 | 105 | 7 | 73 |
| 02/15/2025 | 27 | 132 | 28 | 101 |
| 02/22/2025 | 12 | 144 | 11 | 112 |
| 03/01/2025 | 24 | 168 | 31 | 129 |
I have a report that calculates year-over-year KPIs, from the first of the year to the current date in the respective year. The end customer now wants a table visual that shows a history of these weekly KPI summaries.
example KPI table data:
| Saturday's Date | Receipts this year | Receipts this year (cumulative) | Receipts last year | Receipts last year (cumulative) |
|---|---|---|---|---|
| 01/04/2025 | 4 | 4 | 5 | 5 |
| 01/11/2025 | 25 | 29 | 17 | 22 |
| 01/18/2025 | 9 | 38 | 9 | 31 |
| 01/25/2025 | 14 | 52 | 8 | 39 |
| 02/01/2025 | 41 | 93 | 27 | 66 |
| 02/08/2025 | 12 | 105 | 7 | 73 |
| 02/15/2025 | 27 | 132 | 28 | 101 |
| 02/22/2025 | 12 | 144 | 11 | 112 |
| 03/01/2025 | 24 | 168 | 31 | 129 |
The measures "Receipts this year", "Receipts this year (cumulative)", and "Receipts last year" are all correct, however the "Receipts last year (cumulative)" is wrong. Notice how there were 31 receipts last year during the 03/01/2025 bucket, yet there are only 17 more transactions week over week in last year's cumulative column. The correct number should be 143, not 129, in the "Receipts last year (cumulative) column.
I found that some of the transactions in the 03/01/2025 bucket landed on 02/29/2024, and since there is no 02/29/2025, the measure doesn't return a value for the 14 transactions from 02/29/2024.
I need help creating a cumulative total measure that basically just sums whatever "Receipts last year" outputs.
Here are my dax measures:
Calendar =
var _calendar =
CALENDAR("1/1/2019", DATE(YEAR(TODAY())+3, 12, 31))
RETURN
ADDCOLUMNS(
_calendar,
"ISO Week Number", WEEKNUM([Date],2)
,"Saturday's Date", [Date] + (5 - WEEKDAY([Date],3))
)
Receipts last year =
CALCULATE(
DISTINCTCOUNT('InventoryTable'[transaction number])
,DATEADD('Calendar'[Date],-1,YEAR)
,'Calendar'[Date] <= TODAY()-365
,'InventoryTable'[Transaction Type] IN {"Inbound 1","Inbound 2"}
)
Receipts last year (cumulative) =
VAR __calc =
CALCULATE(
SUMX(
FILTER(
ALLSELECTED('Calendar')
,[Saturday's Date] <= MAX('Calendar'[Saturday's Date])
--&& [Date] <= TODAY()
)
,[Receipts last year]
)
)
RETURN
IF(
SELECTEDVALUE('Calendar'[Saturday's Date]) <= TODAY()
,__calc
,BLANK()
)
Receipts this year =
VAR __date_max = MAXX(ALL('Calendar'), [Date].[Date])
VAR __date_min = DATE(YEAR(TODAY()), 1, 1)
RETURN
CALCULATE(
DISTINCTCOUNT('InventoryTable'[transaction number])
,FILTER(
'Calendar'
,[Date].[Date] >= __date_min
&& [Date].[Date] < __date_max
)
,FILTER(
'InventoryTable'
,[Transaction Type] IN {"Inbound 1","Inbound 2"}
)
)
Receipts this year (cumualtive) =
VAR CurrentDate = MAX('InventoryTable'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('InventoryTable'[transaction number]),
FILTER(
ALLSELECTED('InventoryTable'),
'InventoryTable'[Date] <= CurrentDate
&& YEAR('InventoryTable'[Date]) = YEAR(TODAY())
)
,'InventoryTable'[Transaction Type] IN {"Inbound 1","Inbound 2"}
)
The solution to the leap year problem I found was to change 02/29/YYYY to 02/28/YYYY. This is not a perfect solution, but it was the only thing I could think of.
Here is the foreign key I made to join to the calendar table
date_fk =
IF(
[Date].[MonthNo] = 2
&& [Date].[Day] = 29
,DATE([Date].[Year],2,28)
,[Date].[Date]
)
I welcome anyone to come up with a "solution" that does not require me to alter the data in the table. If anyone knows how to do this within a cumulative total measure itself that would be better.