Forum Discussion
how do i get last week sale for same period when I don't have the dates column
Hi there,
I have data in this format:
- One column has the year.
- The second column has the period (12 periods in a year, but they are not the same as months, as they start on different dates).
- The third column has the week number (every period has at least 4 weeks; some of them have 5 weeks).
I'm trying to create filters for the data:
The first filter is the year, followed by the period and then the week. The data I get after applying these filters is correct.
Now, I've tried using the 'sameperiodlastyear' function to retrieve data from the previous year with the same period and week, but it's not working. Can you please help me?
Thanks.
- Are you using Power BI? You'd need a date dimension table that maps the days in a year to the periods. If you do a search for "power bi date table" there should be quite a few examples. If your data is coming out of an accounting system, it'll often have a date table you can use somewhere in the database.
- I created a data table, but I noticed that the dates in the periods are different. Let me explain what I mean by "dates different in the period."
For example, we are currently in period 1, week 1, which spans from September 30 to October 6. However, in the previous year, period 1, week 1, spanned from October 1 to October 10. The dates from September 30 to October 6 last year fell in period 12, week 4.
Now, when I try to use the sameperiodlastyear function, it returns a blank or an error every time. My goal is to compare data on a period-to-period basis, for example, comparing period 1, week 1, with period 1, week 1, without considering the specific dates.- The reason "sameperiodlastyear" isn't working for you is because it doesn't know what last year is in your data because you have no date reference. The date/time-intelligence features require a date table and I believe you even have to designate them as a date table in the relationship view (it's in the right-click menu). Date tables require a sequential list of dates in order to be marked as a date table (it'll give an error if you try on something else).
The other reason you'd need dates is so that you can get the relative week in order to filter on something like "last week". The function to get "now" in Power Query as a comparison returns a date/time value, usually that's what you use to get a relative date/week/month for your date table. Unless your data has some reference to what the current period is... if it does you could use that to create a "relative period" column to filter on (where 0 is the current period, -1 is the last period, etc) and bypass the date/time intelligence functions by just filtering on that value.
yes, i am using powerbi,
do i need to create dates in data table even though i don't have data according dates?
