Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ken-BI
Regular Visitor

Same day last year area graph.

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
pawel1
Skilled Sharer
Skilled Sharer

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)

PY uptolastsellingdate.JPGlet me know if works

 

 

How about write a new measure for last year. 

 

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.