Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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 😀😆