Prior Year Sales using 454 Calendar with Date Slicer

[Anonymous]

Hi, currently we have an excel workbook ran from Jet that gives sales data, ie Actual, PY, Budget, Target, etc. We are a retail business, with our weeks starting on Sunday. In this workbook (which I'm trying to replicate in Power Bi) the Prior Year totals are giving me some trouble. Due to I have to use the same day last year and not the same date, is giving me grief. I do want them to be able to use the date slicer to look at the daily, week to date, month to date, as well as year to date totals for each location.

 As an example - the report date is 7/1 as we look at the day prior to get a full day.

I can get either the Daily total correct or maybe the Year, but I can't seem to figure out what would be the right path to take to make this function correctly with the date slicer. I am using the Table Visual. I've taken a screen shot of how I want this to work, but not sure if it's totally possilbe using the slicer or not. 

I'd really appreciate an advise or simply some direction I would need to take. 

Daily report 

Report Date	7/1/2020
Day Last Year	7/3/2019

Week to date

Report Start Date	6/28/2020
Report End Date	7/1/2020
Curr Date	6/28/2020..7/1/2020
Last Year Start	6/30/2019
Last Year End	7/3/2020
Prior Date	6/30/2019..7/3/2019

Month to Date

Report Start Date	7/1/2020
Report End Date	7/1/2020
Curr Date	7/1/2020..7/1/2020
Last Year Start	7/1/2020
Last Year End	7/1/2020
Prior Date	7/1/2019..7/1/2019

Year to Date

Report Start Date	1/1/2020
Report End Date	7/1/2020
Curr Date	1/1/2020..7/1/2020
Last Year Start	1/1/2019
Last Year End	7/1/2019
Prior Date	1/1/2019..7/1/2019

[Anonymous]

Hi  @Anonymous,

Create 2 measures as below:

Month to Date and Year to Date are the same:

Month  to date = 
var _calendardate=CALCULATETABLE(VALUES('calendar Table'[Date]),'calendar Table'[Date] in FILTERS('Table'[Date]))
Return
CALCULATE(MAX('calendar Table'[Date]),SAMEPERIODLASTYEAR(_calendardate))

And you will see:

Day Last Year  and Week to date are the same:

Day Last Year = 
var _calendardates=CALCULATETABLE(VALUES('calendar Table'[Date]),'calendar Table'[Date] in FILTERS('Table'[Date]))
Return
CALCULATE(MAX('calendar Table'[Date]),FILTER('calendar Table',YEAR('calendar Table'[Date])=YEAR(MAXX(_calendardates,'calendar Table'[Date]))-1&&'calendar Table'[weeknum]=WEEKNUM(MAXX(_calendardates,'calendar Table'[Date]),1)&&'calendar Table'[weekday]=WEEKDAY(MAXX(_calendardates,'calendar Table'[Date]),1)))

And you will see:

Here is the .pbix file,pls click here.

 
Best Regards,
Kelly

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

[Anonymous]

@Anonymous  Thanks for the insight. I'm assuming I would follow that same path for the day and week as well?

I would like to achieve all of it in one measure. Is that possible?

[Anonymous]

Also, I'm not trying to see the date I want the PY sales numbers

[Anonymous]

This is what I have worked on, and it's not working. With the Week to Date, the code below is the only one I have succesfully been able to use and it work correctly, so I was hoping to be able to change it with subtracting the year. I can get the full same week from last year, but I need it to show the Week to date. 

PY Sales =

var _calendardateMonth = SAMEPERIODLASTYEAR('454 Date Table'[Date])

var _calendardatesYear = SAMEPERIODLASTYEAR('454 Date Table'[Date])

var _calendardateWeektoDate = DATESBETWEEN('454 Date Table',LASTDATE('454 Date Table'[Start of Week]),LASTDATE('454 Date Table'[Date])-365)

var _calendardateDay = DATEADD('454 Date Table'[Date],-1,DAY)

RETURN

CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),_calendardateDay,_calendardateWeektoDate, _calendardateMonth, _calendardatesYear)

[Anonymous]

Hi  @Anonymous ,

Can you provide some sample data with expected output?

 
Best Regards,
Kelly

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

[Anonymous]

@Anonymous 

So, I've taken screen shots of the Jet Report (I've also included the date factor that drives this report) that is being used, and also screen shots of my Power Bi file that I'm trying to replicate.

Currently this is my measure

CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]), DATEADD('Date Table'[Date], -364,DAY)

The Daily and Week to Date works, but then if  you look at  the Month to date and Year to date from the Jet and PowerBi screen

shots my totals are off.

Seems, when I get into doing variables, I can only get a sum total and not for each of the locations as its working now.

Any advise would be greatly appreciated. Thanks!!

shots

[v-kelly-msft]

Hi   @Anonymous 

Sorry for the late reply!

It looks like a measure totals problem. A very common issue. See below post about it

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 You can add an extra measure while calculating the totals.Such as :

year to date=sumx(values('Date Table'[Date]),[measure]) 

 

Best Regards,

 Kelly

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

[Anonymous]

@Anonymous although those articles were intersting, and could help out in future situations, I don't believe that is my issue.

My issue lies with the date. As we are looking for Prior Year sales, my issue is that we look at the day and not the date. So, using SAMEPERIODLASTYEAR does not work for my situation for looking at the day last year or the week to date last year. It works just fine for looking at the month or year. As I explained before, my current data is correct when I select the day and the week to date but my numbers are off when I look at the month total or the year total. For example if I choose July 14, 2020 (Prior Year date would be 7/16,2019) on my date slicer with my current measure CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),DATEADD('Date Table'[Date], -364,DAY)) I get correct results. If I change the date slicer to July 12, 2020 to July 14, 2020 which that would be the week to date (Sunday starts the week) (Prior Year date would be 7/14/2019 to 7/16/2020) my totals are correct. Now, when I select July 1, 2020 to July 14, 2020 with my slicer, that's when my totals are incorrect. As shown above in my table, each location should have a total value for each row, then the sum of those values at the bottom of the table. Same thing happens with I select January 1, 2020 to July 14, 2020 with the slicer my numbers are off. 
I've tried writing variables but either I get a blank value on each row or I get a sum value repeating itself on each of the rows.
I'm just trying to find the best solution, that I can create a measure that will cover all these sceniros as the user will typically select these 4 options with the date slicer, but I need the date slicer so they can look at any date range. Thanks again for your help!

[v-kelly-msft]

Hi  @Anonymous ,

Can you upload your .pbix file to Onedrive business and share the link with me?

If you take the suggestion in my last reply to create a measure using sumx funtion ,it will give you the right result,but I‘m not sure whether  the 1 measure can be used in 4 different criteria,so pls share your file with me ,I will test it.

Best Regards,
Kelly

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

[Anonymous]

@Anonymous, so I've been working in a different direction, but I know something isn't correct with my RETURN statement.

I get the correct value if I leave off the second IF statement. I can get all correct totals when I change out my variables, but I can't seem to get all 4 variables to work in my RETURN Statement. As it's written right now, I get my Day and Week, but the Month and Year are off just a tad, but if I create a measure with the SAMEPERIODLASTYEAR and use it seperately the totals are correct. Any thoughts?

var CalendarDateMonth = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),SAMEPERIODLASTYEAR('Date Table'[Date]))

var CalendarDateYear = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]), SAMEPERIODLASTYEAR('Date Table'[Date]))

var CalendarDateWeek = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),DATEADD('Date Table'[Date], -364,DAY))

var CalendarDateDay = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),DATEADD('Date Table'[Date], -364,DAY))

RETURN

IF(SELECTEDVALUE('Date Table'[Date],CalendarDateDay),CalendarDateWeek,

IF(SELECTEDVALUE('Date Table'[Date],CalendarDateMonth),CalendarDateYear))

[Anonymous]

@Anonymous I'd rather not upload my file as there is sensative information. I tried your suggestion 

PY Total = SUMX(VALUES('Date Table'[Date]),([PY Sales])) but I got blank column. 

[Anonymous]

@Anonymous actually I did get your suggestion to work. I found a typo (sorry about that). However, I'm still off on my month to date and year to date for the prior year. 

[lbendlin]

For Daily and Weekly use DateAdd() with days rather than years.  You'll need to keep a reference table somewhere that has the day difference between your years, for each year.  So between this year and the prior year the date difference is 364.

For Monthly and Yearly you can use the regular functions. SAMEPERIODLASTYEAR plus the "is in the past"  flag.