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 ...
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

     

     

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

 

 

mskarasha's avatar
mskarasha
Frequent Visitor
4 years ago

This worked perfectly, thank you!