YTD for all prior years

[jtma508]

I'm trying to build a report that shows annual metrics (sales, orders, etc.) going back several years.  For each prior year I would like to show the metrics not just for the complete year, but also for the same period as the current year.  So, if the current year happens to be through day 275 of the year, I'd like to show the metric values for each prior year through day 275.  I've added a day-of-year value to my Dates table and attempted to utilize that with no luck .  I've also tried the Measure:

YTD Prev Sales = CALCULATE([Total Sales],DATEADD(DATESYTD('Dates'[Date]),-1,Year))

But that only appears to give me the value for last year and only displays it on the 'Total' row.  

I'm at a loss.  Anyone?  Thanks!

[jtma508 · Accepted solution]

After being repeatedly thwarted by the Time-Intelligence functions I had to brute-force a solution.  Again, I added a column to my Dates table for Day-of-Year.  Inelligant although my soluion may be, it works.  Typically you'd place a +1 after the DATEDIFF call but our data posts through the previous day so it works as written:

YTD PS =

VAR _SOY="1/1/" & YEAR(TODAY())

VAR _DOY=DATEDIFF(_SOY,TODAY(),DAY)

VAR SPPY=

CALCULATE([Total Sales],FILTER('Dates','Dates'[DOY]<=_DOY))

RETURN

SPPY

[Ashish_Mathur]

Hi,

If your Dates table goes only till today's date, then then measure should work

YTD Prev Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Dates'[Date]))

[jtma508]

as I mentioned in my original post.  Although I tried DATESYTD, I've also tried SAMEPERIODLASTYEAR and again, it only gives the value for the most recent Year (doesn't go year-by-year) and displays the value in the Totals row.

[Ashish_Mathur]

I'll need to see your file.  Share the download link and show your expected result.

[samdthompson]

Hello, try something like this:

Sales Amount Cumulative :=

VAR LastVisibleDate = MAX ( 'Date'[Date] )

VAR FirstVisibleDate = MIN ( 'Date'[Date] )

VAR LastDateWithSales = CALCULATE ( MAX ( 'Sales'[Order Date] ))

VAR Result =

    IF ( FirstVisibleDate <= LastDateWithSales,

        CALCULATE (

            [Sales Amount],

            'Date'[Date] <= LastVisibleDate))

RETURN

    Result

if you put that on a chart and with year in the legend and say week number or month name in the axis you sould be all good

[jtma508]

No joy.  Just gives me each year's total sales.  My Dates table is built from the Sales table so only includes dates between the date of the company's first sale and most recent sale.  

[samdthompson]

oh, you need a proper date table. Make one of those first.

DateTable=CALENDARAUTO()

[jtma508]

I have a 'proper' Date table covering all dates between the company's first and most recent purchases.

[Greg_Deckler]

@jtma508 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...