Forum Discussion

badluckmath's avatar
badluckmath
Frequent Visitor
5 years ago
Solved

Relative Value Between Dates

I want to get the relative value between dates in the table below:     My goal is to create an cars showing  the relative values in the column "Quantidades". Card 1 :  Today/ Yesterday Car...
  • v-xuding-msft's avatar
    5 years ago

    Hi badluckmath ,

     

    Please try the following measures:

     

     

    Today/ Yesterday =
    VAR today_ =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Date] = TODAY () )
        )
    VAR yesterday_ =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Date] = TODAY () - 1 )
        )
    VAR NextMaxDate_ =
        MINX ( TOPN ( 2, ALL ( 'Table' ), 'Table'[Date], DESC ), 'Table'[Date] )
    VAR NextDateQuan_ =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Date] = NextMaxDate_ )
        ) -- if there is no data yesterday, it calculates the value on next maximum date.
    RETURN
        IF (
            ISBLANK ( yesterday_ ),
            DIVIDE ( today_, NextDateQuan_ ),
            DIVIDE ( today_, yesterday_ )
        )
    
    This week/ Last Week =
    VAR thisweek =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Week] = MAX ( 'Table'[Week] ) )
        )
    VAR lastweek =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Week] = MAX ( 'Table'[Week] ) - 1 )
        )
    RETURN
        DIVIDE ( thisweek, lastweek )
    
    This Month/ Last Month =
    VAR thismonth =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) ) )
        )
    VAR lastmonth =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER (
                'Table',
                MONTH ( 'Table'[Date] )
                    = MONTH ( MAX ( 'Table'[Date] ) ) - 1
            )
        )
    RETURN
        DIVIDE ( thismonth, lastmonth )
    

     

     

    If you don't want to calculate percentage, please try this:

     

    This week VS Last Week =
    VAR thisweek =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Week] = MAX ( 'Table'[Week] ) )
        )
    VAR lastweek =
        CALCULATE (
            SUM ( 'Table'[Quantidade] ),
            FILTER ( 'Table', 'Table'[Week] = MAX ( 'Table'[Week] ) - 1 )
        )
    RETURN
        thisweek & " VS " & lastweek