Daily Count Between Dates for each Region

[mskarasha]

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

[DataInsights · Accepted solution]

@mskarasha,

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

[mskarasha]

This worked perfectly, thank you!