Forum Discussion
badluckmath
6 years agoFrequent Visitor
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...
- 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
v-xuding-msft
Community Support
6 years agoHi 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