Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @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
Proud to be a Super User!
Check out my blog: Power BI em Português
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
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |