Relative Value Between Dates

[badluckmath]

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

Card 2 : This week/ Last Week

Card 3: This month / Last Month

I'm new using Power  Bi, so any help is useful!

[v-xuding-msft · Accepted solution]

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

[amitchandak]

@badluckmath , Try if this can help

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[Greg_Deckler]

@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/3395....
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 

[lbendlin]

please provide the sample data in usable format.