Time intelligence formulae

[Richard76]

Hi Folks,

really struggling with some time intelligence formaulae on power BI. I'm trying to work out sales figures up to the same day the previous year. So in example below I need to compare from 1/1/19/ - 8/1/2019 . I was using

Same Period LY = Calculate( [Total Sales], PREVIOUSYEAR( 'Time'[PK_Date] )) which worked fine when I only had 2 years date ie 2018 & 2019. Now I have 3 years its not working so how do I amend the starting point of the calculation which will be 1/1/2019 ??

[Anonymous]

Hi @Richard76 ,

I assume all your relationships are done between the date and fact table.

So I would do 

Same Period LY = Calculate( [Total Sales], sameperiodlastyear( 'Time'[PK_Date] ))

or

Same Period LY = Calculate( [Total Sales], DATEADD('Time'[PK_Date] ,-1,year)

Let me know if it worked, if so mark as solution.

Best Regards,

DR

[Richard76]

Sorry I'm probably not explaining clearly. I already have a formulae in as you suggested but the issue is I also have a date slicer in as well which runs from 1/1/19  - 8/1/2020 . When I try the formulaes you suggested it gives me the sales for the full year (I have data running from 2018) but I only want from 1/1/19 - 8/1/19 if that makes sense 

[Anonymous]

Hi again @Richard76,

I may have misunderstood, in that case I would use dates between in the calculate formula, like

LY Sales = calculate([total sales],DATESBETWEEN(DATE,MIN(DATE),MAX(DATE)-365)

I'm not sure but I think this might work, let me know 👌

[Richard76]

Getting an error with that formulae 'too few arguments were passed to the DATESBETWEEN function but cant work out where it is wrong 

Sales LY = CALCULATE([Total Sales],DATESBETWEEN('Time'[PK_Date],MIN('Time'[PK_Date],MAX('Time'[PK_Date])-365)

[Anonymous · Accepted solution]

Jesus it's still morning here I can't seem to do what I was thinking 😂
There was a parentisis missing sorry!!

Sales LY = CALCULATE([Total Sales],DATESBETWEEN('Time'[PK_Date],MIN('Time'[PK_Date]),MAX('Time'[PK_Date])-365)

BR,

DR

[Richard76]

Thanks you so much this works a treat and is so much easier than some of the other 'solutions' I was trying to follow online. 

Now get some sleep 😀😆