Hello community,
Need some help with a DAX calculation. I need to calculate prior period sales. Prior periods is same number of days before the current period start. E.g in the screenshot. Also I need to analyse the sales by DayOfWeek (Mon, Tue). And if I use DATEADD function for my calculation , it gives me “contagious error” when i analyse it by Day of the week.
I am attaching the pbix file.
Thank you !
EDNA_PriorPeriod.pbix (22.9 MB)
I’m confused by the visual you’re showing - are you trying to show the prior period sales day by day - so the first row should show Sales for the 10th of February under OP Sales Units, and for the 22nd of January for the PPTest?
Normally when I use prior period sales, it’s as a sum total - so this is the basic pattern I use:
eDNA Solution - Prior Period =
VAR _Start = [Prior Period Start]
VAR _End = [Prior Period End]
RETURN
CALCULATE( [Opp Sales (Units)],
DATESBETWEEN( DimCalendar[Date], _Start, _End ))
but this will not work on a visual that needs to display the sales day by day - so please clarify your requirement.
Hi Heather, Thanks heaps for looking into it. Can there not be one calculation which can be used if I want to show the PP sales by date or aggregated ? Also , as I mentioned in my post, I am having issues when I aggregate the data by day of week.
I think I’ve got it - here are the steps I’ve taken, and some recommended changes (you may have some additional date logic that isn’t needed here)
eDNA Prior Date Value =
VAR _Days =
DATEDIFF(
MINX( ALLSELECTED(DimDatesCustom), DimDatesCustom[Date] ),
MAXX( ALLSELECTED(DimDatesCustom), DimDatesCustom[Date] ),
DAY ) + 1
RETURN
CALCULATE( [Opp Sales (Units)],
REMOVEFILTERS(DimDatesCustom),
DATEADD( DimCalendar[Date], _Days * -1 , DAY ))
the REMOVEFILTERS step was necessary because both the DimCalendar and the DimDatesCustom are filtering the table, and we have to remove the DimDatesCustom.
Here is the file with that solution:
eDNA Solution - PriorPeriod.pbix (22.9 MB)
I recommend reviewing to see if you really need all of the calendar tables that you have in the model - because for this particular solution, I don’t think it’s necessary. I do understand that it might be needed for other portions of your report - but it is complicating tasks like changing the date range addressed by functions like DATEADD.
Thanks Heather ! I had a look at the file. Issue with DATEADD function is that when i remove the date from the visual and only keep DayOfWeek , it gives the following error.
To your point, i agree that DimDateCustom table is not required. The only reason i have to keep it because I have Timeframe and Custom Date bookmark in the report. Which allows users to switch between selecting timeframes and custom dates. When I used DimCalendar for both Timeframe and CustomDate , bookmark was not working properly.
The reason why I have DimTimeframe table is as it contains custom timeframes for users. Would be happy if you have recommendation on simplifying this.
Based on your custom time period requirement - I agree that you may need to keep the extra date tables.
I if you are doing that, it is very important that you always mark your main calendar (in this case DimCalendar) table as a date table in the model. If you don’t do this, the model will be bloated as Power Bi adds a separate invisible calendar table to every date column in your model.
it’s actually ALWAYS important to do this, but particularly since you are adding date columns here
So, you are correct that DATEADD can’t handle what your wanting to do - because it needs to be able to find a continuous date range in the data it’s reviewing.
note that if you change your filter to cover a single week (so that two Mondays don’t get added together) - then this will work:
But I’m guessing from your model that you need something more like this:
I will look into creating a custom version of DATEADD and get back to you. This may take a few hours, as I’m not sure I can fit this in during lunch.
Thanks Heather. This is more than I expected in any forum, to be honest. Really appreciate your support.
You are right, when there is only 7 days selected, Weekdays work fine. As soon as more number of days are selected I get contagious date error. Would be great if you could help there.
Meanwhile I am coming up with an alternative to timeframe and custom bookmark to get rid of DimCustomndate table. Thanks for your suggestion on the marking the date table.
Hello @gracejoshi
Good to see that you are having progress with your inquiry. Did the responses from @Heather help you solve your original question?
Please note that if your original question has been answered within the forum it is important to mark your thread as ‘solved’. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum.
Sorry this has taken so long @gracejoshi - life got in the way a bit this weekend.
I kept hitting my head on this one, until I realized that we had marked the wrong table as a date table (given your filtering logic on this report) - once I fixed that, it was easy. 
Mark the DimDatesCustom calendar as your date table, and then your measure is this:
eDNA New Prior Date Value =
VAR _Days =
DATEDIFF(
MINX( ALLSELECTED( DimDatesCustom ), DimDatesCustom[Date] ),
MAXX( ALLSELECTED( DimDatesCustom ), DimDatesCustom[Date] ),
DAY ) + 1
VAR _StartDate = MINX( ALLSELECTED( DimDatesCustom ), DimDatesCustom[Date] ) - _Days
VAR _EndDate = MINX( ALLSELECTED(DimDatesCustom), DimDatesCustom[Date] ) - 1
RETURN
CALCULATE( [Opp Sales (Units)],
FILTER( ALL(DimDatesCustom),
DimDatesCustom[Date] >= _StartDate &&
DimDatesCustom[Date] <= _EndDate ))
and here’s the final file:
eDNA Solution2 - PriorPeriod.pbix (22.9 MB)
Thanks Heather !! Love the solution !! 
Hi @Heather ,
There is some issue that I am facing as I have simplyfied the model to remove DimCustomDates. I have marked the DimCalendar as Date table as I do not have DimCustomDates anymore. Now I am using timeframe table for timeframe selection, which has custom option as well.
I tried couple of things but they dont seem to be working well in all scenarious. I have attached .pbix with your original dax.
Could you help me with this please
eDNA Solution - PriorPeriod_SimplyfiedDates.pbix (22.9 MB)
?
Thanks
