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

Postby dan_cooper » Sat Oct 31, 2020 9:27 am

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

Postby MattAllington » Sun Nov 01, 2020 6:47 am

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]))

You also may find this article useful https://exceleratorbi.com.au/dax-time-i ... beginners/
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

Postby dan_cooper » Mon Nov 02, 2020 1:58 am

Thank you so much for your help with this.

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests

cron