Forum Discussion
Problem with DATESBETWEEN that has a variable inside
This is a lot to take in 😀.
I would start by creating 2 measures to return the desired start and end dates based on the slicer selection. You can then put these into a table / matrix visual and see what they are returning, and you can also use them within the YTD calculation. That will allow you to determine where the underlying issue is - either the calculation of the dates, or applying those dates as filters.
Once you've identified the problematic code, post the definition of the measure and a screenshot of the model relationships, ideally highlighting tables which are used for slicers so that we can get an overall picture of what is going on.
Sorry, my bad for the typo, a DateTable was created.
They say an image is worth 1000 words, not sure how much that is the case here 🙂
This is something that was build in another report, that used another logic all together (weeks, that were combined with years, and interpreted as integers and not dates)
But this is what needs to be built:
YtD sales - easy, sales from the begining of whatever is selected as the current year [Hardcoded Start date which is the 1st January of the selected year up to Max of the date slicer selection]
Seasonal sales - a season can overlap on 2 years, so items that are considered as being part of season Spring 2024 for example, are usually sold between november 2023 up to August 2024, or May N up to Janaury N+1 for Winter collection [slicer is setting up the min and max range ]
Never out of stock items / Caryover - based on the season selection (Spring or Winter) this will either sum up sales for the first semster or the 2nd semester in the year [hardcoding the start and end date].
So ie: if the user has selecter in the slicer 5th of november 2023 up to the 19th July 2024 and the season slicer is set to Spring 24
The YtD calculation should return the sum of all sales since the 1st jan 2024 up to the 19th july 2024
The seasonal calculation should return the sales of only Spring item (i didn't put this part in the code, as it's not problematic, a simple filter() does the job for the season/liability dimension) for spring 24 sales that took place between 5th of november 2023 up to the 19th July 2024
The Caryover calculation should return the sales of caryover items (season dimension), and since Spring 2024 was selected, it should take sales from the 1st of jan (hardcoded) up to the 30 june (hardcoded in the variable when selecting spring 2024) ==> and this is currently the issue, even if it's capped via the variable, it will still take the max range, so it will return sales from 1st of jan 24 up to the 19th july (instead of 30th june) for Caryover items.
IDK if this made it easier or more complex to understand 🙂
This is a lot to take in 😀.
I would start by creating 2 measures to return the desired start and end dates based on the slicer selection. You can then put these into a table / matrix visual and see what they are returning, and you can also use them within the YTD calculation. That will allow you to determine where the underlying issue is - either the calculation of the dates, or applying those dates as filters.
Once you've identified the problematic code, post the definition of the measure and a screenshot of the model relationships, ideally highlighting tables which are used for slicers so that we can get an overall picture of what is going on.
Hmm,
you pointed me in the right direction, it seems that a weird combination of related/unrelated tables with the combination of allowed/unallowed vizualisation cross filtering was causing the error.
(i kind of feel stupid rn)
I've been pulling whatever is left from my hair on this, thank you!