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

Card 2 : This week/ Last Week

Card 3: This month / Last Month

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

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

Relative Value Between Dates.pbix42 KB

4 Replies

lbendlin
Super User
5 years ago
please provide the sample data in usable format.
Greg_Deckler
Community Champion
5 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
amitchandak
Super User
5 years ago
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-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

v-xuding-msft

Community Support

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

Relative Value Between Dates.pbix42 KB

Forum Discussion

Relative Value Between Dates

4 Replies

Related content

DAX Calculation - Find value based on day (relative)

Relative % calculation

Find Most Recent Date Relative to another Date Column in Power Query

Time Relative filter

measure relative value dont work