Forum Discussion
Time intelligence formulae
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
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
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
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 👌
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)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