Prior Period Calculation - Unsolved

DAX DAX Showcase
1

Hi Community,

I raised a question previously on prior period calculation. A part of it was solved but i marked it as solved. I need help with my prior period calculation.

I have a timeframe filter and custom date filter. Custom date filter need to be cleared before using.

I want to get the sales for same number of days immediately prior to current period selection. e.g if the current date selection is 20-Feb-2023 thru 28-Feb-2023, then I want my prior period number to be from period 11-Feb-2023 thru 19-Feb-2023.

I am attaching pbix file with the model.

Thanks a lot.

Regards
Grace
eDNA Solution - PriorPeriod_SimplyfiedDates.pbix (22.9 MB)

image
image1977×1101 221 KB

2

Bumping this post for more visibility from our experts and users.

3

@gracejoshi Hello there! I had a similar problem with one of my reports I did at work.

In your pbix file I added “IsLastWeek” as a column in your Date Table.

IsLastWeek =
var vLastWeek = CALCULATE(MAX (‘DimCalendar’[SalesWeekEnding]),
FILTER(ALL(‘DimCalendar’), ‘DimCalendar’[Date] = MAX(‘DimCalendar’[Date])-6))

RETURN
IF(‘DimCalendar’[SalesWeekEnding] = vLastWeek, “Y”, “N”)

Then I dropped this in the filter selection in the table and selected Y

image
image1436×654 45.4 KB

But you are wanting Sales to appear, correct?

image
image1025×142 8.78 KB

This is the DAX I inserted into the table. Do the numbers look right?

OPP Sales PW =
VAR CurrentWeek = SELECTEDVALUE(‘DimCalendar’[Weekno] )
VAR CurrentYear = SELECTEDVALUE(‘DimCalendar’[CalendarYear] )
VAR MaxWeekNumber = (CALCULATE( MAX (‘DimCalendar’[Weekno]), ALL(‘DimCalendar’)))

RETURN
SUMX(
FILTER( ALL(‘DimCalendar’),
IF(CurrentWeek = 1,
‘DimCalendar’[Weekno]= MaxWeekNumber && ‘DimCalendar’[CalendarYear] = CurrentYear -1,
‘DimCalendar’[Weekno] = CurrentWeek - 1 && ‘DimCalendar’[CalendarYear] = CurrentYear )),
[Opp Sales (Units)])

The context was looking for the week number in order for you to find the previous week. So you can see that here in the code.

Paul

1 Like
4

Hello @gracejoshi

Did the response above from @Paul.Gerber (thank you!) help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

5

Hi @Paul.Gerber

Do i need to have separate calculation for weekending ? Cant i have one calc which can be rolled up and down for date heirarchy (date, weekday, weekend, month etc) ?

6

@gracejoshi No. I used what was in your pbix file. I have uploaded it for you to look at.

eDNA Solution GERBER - PriorPeriod_SimplyfiedDates.pbix (22.8 MB)

7

Hi @gracejoshi

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!