Forum Discussion

mskarasha's avatar
mskarasha
Frequent Visitor
4 years ago
Solved

Daily Count Between Dates for each Region

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:

DATEDISTRICTCUMULATIVEDAILY COUNT
6/15/2022    District 1        193611466
6/15/2022     District 2       10328876
6/15/2022     District 3        7229735
6/16/2022     District 1      20521           1160             
6/16/2022    District 2     10943615
6/16/2022    District 3     7724495

...(remaining June dates)

DATEDISTRICTCUMULATIVEDAILY COUNT
7/01/2022       District 1       1501                 1501                  
7/01/2022District 2718718
7/01/2022District 3505505
7/02/2022District 128081307
7/02/2022District 21252 534
7/02/2022District 3703 198

 

 

Need Help
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    4 years ago

    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

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    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