Cumulative Sales, Year on Year

[colverjustin]

Hi all,

I'm losing the plot a bit here, going round in circles trying to solve me problem but Im so stuck! Any help would be amazing.

We have holidays running in 2023. Currently the date is May 2023. I want to be able to look at all bookings that have taken place over the last few years for holidays in 2023, up to the current booking date.

However I then need to compare this to our holidays in 2022, and the comparison would be against all bookings we'd had by May 2022 (including in all the previous years).

In my table I have booking date and departure year (i.e. year of the hoiday), and a bookings value.

Can I create a measure which lets me then bring in departure year into the columns, and to-date bookings into the value, where it only shows up to May of each departure year?

I've included an Excel table to clarify what I'm after.

Thank you for any help, in advance!

[amitchandak]

@colverjustin , Join Booking date with date table. and in visual use Year and month on row from date table and departure year in the column from your table

YTD Sales = CALCULATE(SUM(Table[Booking Values]),DATESYTD('Date'[Date],"12/31"))

or

YTD QTY forced=
var _max =  maxx(allselected('Table'),'Table'[Booking Date])
return
if(max('Date'[Date])<=_max, calculate(SUM(Table[Booking Values]),DATESYTD('Date'[Date])), blank())

//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

You can also use the Window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

TI way

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s