% Difference between two date value

[sewaktamang]

How to get % Change between current Week and Previous Week?
Any solution ?

[Zubair_Muhammad · Accepted solution]

@sewaktamang

It gives me correct values

Did you try this formula

Column =
VAR Priorday =
    CALCULATE (
        MAX ( TableName[Week] ),
        FILTER ( ALL ( TableName ), TableName[Week] < EARLIER ( TableName[Week] ) )
    )
RETURN
    DIVIDE (
        TableName[Conversions],
        CALCULATE (
            SUM ( TableName[Conversions] ),
            FILTER ( TableName, TableName[Week] = Priorday )
        )
    )
        - 1

[Zubair_Muhammad]

@sewaktamang

If your Weeks are simple dates with 7 days gap then this calculated column should work

Colum =
VAR Priorday =
    PREVIOUSDAY ( TableName[Week] )
RETURN
    DIVIDE (
        TableName[Conversions],
        CALCULATE (
            SUM ( TableName[Conversions] ),
            FILTER ( TableName, TableName[Week] = Priorday )
        )
    )
        - 1

[sewaktamang]

 

It gives -100% @Zubair_Muhammad

[Zubair_Muhammad]

Hi @sewaktamang

Please try with this minor modification (HIghlighted in RED below)

Colum =
VAR Priorday =
    PREVIOUSDAY ( TableName[Week] )
RETURN
    DIVIDE (
        TableName[Conversions],
        CALCULATE (
            SUM ( TableName[Conversions] ),
            FILTER ( ALL ( TableName ), TableName[Week] = Priorday )
        )
    )
        - 1

[sewaktamang]

PREVIOUSDAY ( TableName[Week] )

THis is giving Empty Column  

[Zubair_Muhammad]

@sewaktamang

my mistake. PreviousDays do not exist since there are gaps in dates

Please use the revised formula. I provided above. It shall work hopefully

[sewaktamang]

It is giving me date value 😞

Column

Friday, 29 December 1899

Saturday, 30 December 1899

Saturday, 30 December 1899

Saturday, 30 December 1899

Saturday, 30 December 1899

[Zubair_Muhammad · Accepted solution]

@sewaktamang

It gives me correct values

Did you try this formula

Column =
VAR Priorday =
    CALCULATE (
        MAX ( TableName[Week] ),
        FILTER ( ALL ( TableName ), TableName[Week] < EARLIER ( TableName[Week] ) )
    )
RETURN
    DIVIDE (
        TableName[Conversions],
        CALCULATE (
            SUM ( TableName[Conversions] ),
            FILTER ( TableName, TableName[Week] = Priorday )
        )
    )
        - 1

[Zubair_Muhammad · Accepted solution]

@sewaktamang

Could you show me the screenshot of your formula?

[Zubair_Muhammad]

@sewaktamang

A small improvement to this formula so that you do not get -100% for firstweek/firstdate

HIghlighted in RED font below

Column =
VAR Priorday =
    CALCULATE (
        MAX ( TableName[Week] ),
        FILTER ( ALL ( TableName ), TableName[Week] < EARLIER ( TableName[Week] ) )
    )
RETURN
    IF (
        NOT ( ISBLANK ( Priorday ) ),
        DIVIDE (
            TableName[Conversions],
            CALCULATE (
                SUM ( TableName[Conversions] ),
                FILTER ( TableName, TableName[Week] = Priorday )
            )
        )
            - 1
    )

[Zubair_Muhammad]

Hi @sewaktamang

My apologies.

Please use this formula

This will get you correct results

Column =
VAR Priorday =
    CALCULATE (
        MAX ( TableName[Week] ),
        FILTER ( ALL ( TableName ), TableName[Week] < EARLIER ( TableName[Week] ) )
    )
RETURN
    DIVIDE (
        TableName[Conversions],
        CALCULATE (
            SUM ( TableName[Conversions] ),
            FILTER ( TableName, TableName[Week] = Priorday )
        )
    )
        - 1