Forum Discussion
Relative Value Between Dates
- 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
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