Create a measure that increases over time

[Syndicate_Admin]

Hello

I am making a report about some users and I need to make an annual graph that shows the increase / decrease depending on the registrations and registrations of users in each month. So far I've managed to do this:

Active users = CALCULATE ( COUNT ('TABLE'[CASE NO. ] ) , FILTER( ALLEXCEPT ('TABLE' , 'TABLE'[DATE LOW]) ,'TABLE'[DATED]<= Max( 'TABLE' [DATE ADDED] ) ) )

I'm pretty new to Power Bi, and I still miss quite a few things.

Thank you very much for your help!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use the following measure formula to calculate difference between the current date and the previous date:

Active users =
VAR currDate =
    MAX ( 'TABLE'[DATED] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR currCount =
    CALCULATE (
        COUNT ( 'TABLE'[CASE NO. ] ),
        FILTER (
            ALLSELECTED ( 'TABLE' ),
            'TABLE'[DATED] <= currDate
                && FORMAT ( 'TABLE'[DATED], "yyyymm" ) = FORMAT ( currDate, "yyyymm" )
        ),
        VALUES ( 'TABLE'[DATE LOW] )
    )
VAR prevCount =
    CALCULATE (
        COUNT ( 'TABLE'[CASE NO. ] ),
        FILTER (
            ALLSELECTED ( 'TABLE' ),
            'TABLE'[DATED] <= prevDate
                && FORMAT ( 'TABLE'[DATED], "yyyymm" ) = FORMAT ( prevDate, "yyyymm" )
        ),
        VALUES ( 'TABLE'[DATE LOW] )
    )
RETURN
    DIVIDE ( currCount - prevCount, prevCount )

Regards,

Xiaoxin Sheng