Same day last year area graph.

[Ken-BI]

I created an area graph that shows the current YTD info that is grouped by month. Since I do not have a date range specified, the graph contnues to expand as the data is refreshed each night. The year is specidifed in another field. 

The challenge I have is with having last year’s data only show up to the same day last year.  Since there is data for the entire year, it shows all the data for last year.  I do not want to change a date range every day, I would like for it to automatically move forward as each day passes.  Any help would be greatly apprecaited.

Column type: Date 

Direct query

[Ashish_Mathur · Accepted solution]

Hi,

I assume that:

1. You have a calendar table
2. There is a relationshop between the date column of your base data table to the Date column of your calendar table
3. In the calendar table, extract the month and year using the =FORMAT(Calendar[Date],"mmmm") and =YEAR(Calendar[Date]) formulas
4. In your visual/filter, drag month and year from the calendar table
5. Write these measures:

Revenue=SUM(Data[Sales])

YTD sales=CALCULATE([Revenue],DATESYTD(Calendar[Date],"31/12")

YTD sales last year=CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

In your visual, drag the YTD sales and YTD saes last year fields.  Also, as you can observe, i have assumed that your Financial Year ending is 31/12.  Please chage that to your actual year ending date and month.

Hope this helps.

[pawel1]

Hi Ken-BI

I am not an expert, maybe there is a simpler way... but I found here this amazing DAX formula that calculates previous year sales UP TO the corresponding day of current year sales YTD

PY Last Day Selection =
VAR LastDaySelection =
    LASTNONBLANK ('Date'[Date], [Total Sales] )
VAR CurrentRange =
    DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), LastDaySelection )
VAR PreviousRange =
    SAMEPERIODLASTYEAR ( CurrentRange )
RETURN
    IF (
        LastDaySelection >= MIN ( 'Date'[Date] ),
        CALCULATE ( [Total Sales], PreviousRange )
    )

It returns 2016 sales up to the last sales day in 2017, ie Dec-1 2016.  Tomorrow it should refresh automaticaly to Dec-2 2016(assuming there is sales on Dec-2, 2017)

let me know if works

[MattAllington]

How about write a new measure for last year. 

=if([this year measure] >0,[lastyear measure])