Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a table that spans across multiple months that shows the cumulative for each district throughout that month.
I need a calculated column that calculates the daily count for each district.
Once the month ends, it needs to restart. So on July 1st, the daily count is essentially the cumulative amount, since it's day 1.
Example of what I'm looking for:
DATE | DISTRICT | CUMULATIVE | DAILY COUNT |
6/15/2022 | District 1 | 19361 | 1466 |
6/15/2022 | District 2 | 10328 | 876 |
6/15/2022 | District 3 | 7229 | 735 |
6/16/2022 | District 1 | 20521 | 1160 |
6/16/2022 | District 2 | 10943 | 615 |
6/16/2022 | District 3 | 7724 | 495 |
...(remaining June dates)
DATE | DISTRICT | CUMULATIVE | DAILY COUNT |
7/01/2022 | District 1 | 1501 | 1501 |
7/01/2022 | District 2 | 718 | 718 |
7/01/2022 | District 3 | 505 | 505 |
7/02/2022 | District 1 | 2808 | 1307 |
7/02/2022 | District 2 | 1252 | 534 |
7/02/2022 | District 3 | 703 | 198 |
Solved! Go to Solution.
Try this calculated column:
DAILY COUNT =
VAR vDate = Table1[DATE]
VAR vPrevDate =
CALCULATE (
MAX ( Table1[DATE] ),
ALLEXCEPT ( Table1, Table1[DISTRICT] ),
Table1[DATE] < vDate
)
VAR vPrevAmount =
CALCULATE (
SUM ( Table1[CUMULATIVE] ),
ALLEXCEPT ( Table1, Table1[DISTRICT] ),
Table1[DATE] = vPrevDate
)
VAR vResult =
IF (
DAY ( Table1[DATE] ) = 1,
Table1[CUMULATIVE],
Table1[CUMULATIVE] - vPrevAmount
)
RETURN
vResult
Proud to be a Super User!
Try this calculated column:
DAILY COUNT =
VAR vDate = Table1[DATE]
VAR vPrevDate =
CALCULATE (
MAX ( Table1[DATE] ),
ALLEXCEPT ( Table1, Table1[DISTRICT] ),
Table1[DATE] < vDate
)
VAR vPrevAmount =
CALCULATE (
SUM ( Table1[CUMULATIVE] ),
ALLEXCEPT ( Table1, Table1[DISTRICT] ),
Table1[DATE] = vPrevDate
)
VAR vResult =
IF (
DAY ( Table1[DATE] ) = 1,
Table1[CUMULATIVE],
Table1[CUMULATIVE] - vPrevAmount
)
RETURN
vResult
Proud to be a Super User!
This worked perfectly, thank you!
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |