## Push Back Date using 445 periods

Anything related to PowerPivot and DAX Formuale
dan_cooper
Posts: 2
Joined: Sat Oct 31, 2020 9:19 am

### Push Back Date using 445 periods

Hi, I have a problem that i am struggling to solve.

We have a filter to filter out the dates within a 445 period. We then have a column which is for PY balance. of which then takes 1 year off this total.

Please see the below formular of which is on the PY column.

PY Net Balance = CALCULATE([Net Amt Balance],DATEADD('Calendar'[Date],-1,YEAR),all('Calendar'[Date]))

Firstly i would like to know a formular to see what date this is going back to.

Secondly i believe that this should minus by days. As this years period finishes on the Week ending 25/10/2020 where as last years period finishes on the 27/10/2019. Please could you help me with a fomular to get me this result.

Thanks
Daniel

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Push Back Date using 445 periods

Your formula uses an inbuilt time intelligence function. You cannot use these for a 445 calendar. You need to write custom time intelligence for that. There are many ways to do it. One good way is to add a day number of year column (1-366). You can “detect” which days are displayed for this year with VALUES(Calendar[Day Number of Year]). You can then fetch the same days prior year with something like this.

CALCULATE([measure],FILTER(ALL(Calendar),Calendar[Year]=Max(Calendar[year])-1), VALUES(Calendar[Day Number of Year]))

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

dan_cooper
Posts: 2
Joined: Sat Oct 31, 2020 9:19 am

### Re: Push Back Date using 445 periods

Thank you so much for your help with this.

We have now got this to work, just need to taylor to our data