Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Create a measure that increases over time

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!
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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