Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |