Forum Discussion

sewaktamang's avatar
sewaktamang
Frequent Visitor
8 years ago
Solved

% Difference between two date value

  How to get % Change between current Week and Previous Week? Any solution ?
Need Help
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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

     

sewaktamang's avatar
sewaktamang
Frequent Visitor
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    sewaktamang

     

    Could you show me the screenshot of your formula?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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
    )