Forum Discussion

badluckmath's avatar
badluckmath
Frequent Visitor
6 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...
Need Help
Tips and Tricks
  • v-xuding-msft's avatar
    v-xuding-msft
    6 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

     

     
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

badluckmath - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

 

Otherwise, I agree with lbendlin