Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Line chart data comparison for year over year

Hi Everyone,I'm using the below measure as a date picker slicer. Now I need to show year-over-year (2021–2023) revenue in a line chart. If I select last week from the date picker slicer, then all three years of revenue should display in the visual with their last week data, for example, 2022 last week data and 2023 last week data comparison. I tried from the end but was unable to crack it. Can any one help me on this?

MTD/QTD/YTD =
var TodayDate = TODAY()
var Yesterday = TODAY()-1
var Tomorrow = TODAY()+1

var StartofWeek = CALCULATE(MAX('Date'[StartofWeek]),'Date'[Date] = TODAY())
var EndofWeek = CALCULATE(MAX('Date'[EndofWeek]),'Date'[Date] = TODAY())

var MonthStart = CALCULATE(STARTOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate), MONTH('Date'[Date]) = MONTH(TodayDate))
var CurrentMonthEnd = EOMONTH(today(),0)
var LastMonthStart = EOMONTH(TODAY(),-2)+1
//CALCULATE(STARTOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate), MONTH('Date'[Date]) = MONTH(TodayDate)-1)
var LastMonthEnd = EOMONTH(today(),-1)
var NextMonthEnd = EOMONTH(today(),1)

var QuraterStart = CALCULATE(STARTOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate), QUARTER('Date'[Date]) = QUARTER(TodayDate))
var QuarterEnd =  CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate), QUARTER('Date'[Date]) = QUARTER(TodayDate))
var LastQuarterStart = calculate(STARTOFQUARTER('Date'[Date]),'Date'[Date] = TODAY())-92
//CALCULATE(STARTOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate), QUARTER('Date'[Date]) = QUARTER(TodayDate)-1)
var LastQuarterEnd = calculate(STARTOFQUARTER('Date'[Date]),'Date'[Date] = TODAY())-1
// CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate), QUARTER('Date'[Date]) = QUARTER(TodayDate)-1)
var NextQuarterEnd =  CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate)+1,QUARTER('Date'[Date]) = 1)

-- QUARTER('Date'[Date]) = QUARTER(TodayDate)+1)

var YearStart = CALCULATE(STARTOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate))
var YearEnd = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate))
var LastYearStart = CALCULATE(STARTOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate)-1)
var LastYearEnd = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate)-1)
var NextYearEnd = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(TodayDate)+1)
Here are the measueres which i wrote to comapre the revenue
Revenue Current Year = CALCULATE( SUM(RevenueBooked[Revenue]), FILTER( ALL('Date'), 'Date'[Date] >= MIN('Date'[Date]) && 'Date'[Date] <= MAX('Date'[Date]) ) )
Revenue PREVIOUSYEAR = CALCULATE( [Revenue Current Year], SAMEPERIODLASTYEAR('Date'[Date]) )
But Im getting the sale revenue for both years 

 


Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , if you need based on today

     

    This year Today =
    var _min = eomonth(today(),-1*month(today()))+1
    var _max = eomonth(_min,12 -1*month(today()) )
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    Last year Today =
    var _max = eomonth(today(),-1*month(today()))
    var _min = eomonth(_max,-12)+1
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    I think for the current year use only

     

     

    Revenue Current Year = SUM(RevenueBooked[Revenue])

     

    Revenue PREVIOUSYEAR = CALCULATE( SUM(RevenueBooked[Revenue]), SAMEPERIODLASTYEAR('Date'[Date]) )