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
lbendlin
Super User
6 years agoplease provide the sample data in usable format.