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
Anonymous
Not applicable

Year over Year and similar DAX comparison with AS OF Date

Hello,

 

Hopefully, this question is simple seeing as many topics have covered time calculations. Currently, in my report, I have calculations for Year To Date, This Day Last Year, Same Period, Week over Week, Day over Day etc...

 

The problem is that I want to compare those periods as of the SAME DAY last year, not how they finished. So using the "reservation date" I need to compare YTD based on when it was made.

 

For example, if I'm looking at Year to Date (on the books) sales and today is 8/13/2018, I have $10,000. I want to see YTD sales for 2017 as of 8/13/2017. I would even be fine with as of  "same calendar day" last year. So Sales YTD as of day 225 in 2018 vs Sales YTD as of ay 225 in 2017.  Currently, it shows me YTD sales for how 2017 ended which is not a true marker to determine how sales were at this same point last year.

 

Here are my formulas:

 

 Rent_YTD = TOTALYTd([TotalRent],'Date'[Date])

Rent Last Year = CALCULATE([Rent_YTD],PREVIOUSYEAR('Date'[Date]))

 

I need last year to show those sales as of Today last year.

 

Thanks for any help!

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

Believe this is what you need is to create a new date table without any relationship to the other tables then just add the following measure:

 

Rent_YTD_Compared =
VAR Maximum_Date =
    DATE ( YEAR ( MAX ( Report_Date[Date] ) ) - 1; MONTH ( MAX ( Report_Date[Date] ) ); DAY ( MAX ( Report_Date[Date] ) ) )
RETURN
    CALCULATE ( [Rent_YTD_PY]; RentData[Date Sold] <= Maximum_Date )

You have to have a slicer for report date also.

 

Check the PBIX file attach.

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @Anonymous,

 

Try the formula SAMEPERIODLASTYEAR on your CALCULATE:

 

Rent Last Year = CALCULATE([Rent_YTD],SAMEPERIODLASTYEAR('Date'[Date]))

This should return expected result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hey @MFelix,

 

Thanks for responding. The formula you shared does help to compare YTD sales for a specified date range compared to last year sales for that same date range. What I need help with is one step further... Once this is determined, I need this to be "as of today" last year.

 

 

So:

 

YTD 2018 for all sales as of 8/13/2018 (today) or calendar day 225

 

VS

 

SAMEPERIOD Last Year YTD for  all sales made as of 8/13/2017 (today -last year) or last year calendar day 225

 

 

Should I add a calculated column to my reservations table for Same Day Last Year for a filter?

Hi @Anonymous,

 

Just make the difference between the first measure you created and the same period last year thisa should give what you need.

 

Rent Last Year =

CALCULATE([Rent_YTD],PREVIOUSYEAR('Date'[Date])) -
 CALCULATE([Rent_YTD],SAMEPERIODLASTYEAR('Date'[Date]))

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix,

 

Thanks but this still isn't what I'm asking. I do appreciate your help. I can find the difference between the two numbers. I can find SamePeriod YOY numbers as well. I can do this for the same day, same week, same month etc... Once I find that I want to make sure that while I'm like at the same period last year, I want it to be as if I ran my report on the same day as today.

 

Basically, what did my YTD look like on 8/16/2017 not what did it look like "for" 8/16/2017.

 

I'm comparing data to last year but also comparing the point in time in which the data was viewed.

Hi @Anonymous,

 

What do you mean by:

 

"Basically, what did my YTD look like on 8/16/2017 not what did it look like "for" 8/16/2017."

 

Can you explain with some data and expected results? 

Not really sure what is the result you need since you say comparing point in time in wich data i viewed, does your database have incremental refresh?

 

regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix    I hope this helps. I truly appreciate you taking the time to help with this and I hope I'm explaining this correctly. It seems simple in concept but is proving to be difficult. While I'm concerned with seeing the YTD, MTD, WTD or sales for any given date range vs last year for that range. I want to take into consideration the time of the sale. 2017 shouldn't get credit for sales that happened later in the year. 

 

 

Data Info.png

Hi @Anonymous,

 

Believe this is what you need is to create a new date table without any relationship to the other tables then just add the following measure:

 

Rent_YTD_Compared =
VAR Maximum_Date =
    DATE ( YEAR ( MAX ( Report_Date[Date] ) ) - 1; MONTH ( MAX ( Report_Date[Date] ) ); DAY ( MAX ( Report_Date[Date] ) ) )
RETURN
    CALCULATE ( [Rent_YTD_PY]; RentData[Date Sold] <= Maximum_Date )

You have to have a slicer for report date also.

 

Check the PBIX file attach.

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix

 

This is it! Wow, I appreciate you taking the time to help. This is exactly what I need!

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.