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.
Create a proper date table and mark it as a date table. There's plenty of videos and articles about how to do that if you're unsure.
Link the date table to any of the other tables which have date columns.
Create a new column in the date table for Season, probably calculated using the month number but using whatever business logic you need.
You can now use the date column in one slicer and the season column in the other. Everything should just work.
Hi johnt75,
Thank you for you reply.
Not sure what you're trying to say with a proper date table, what i have is a proper date table generated with a calendarauto() for the most part with some additionnal segmentationts.
For the season, this cannot be incorporated in the date table, the season is not a date range, it's based on a item/product (dimension). - as in, a product has a season to id it, but it can be sold at any moment.
Thank you nontheless.
OK, the date table sounds fine. In your original post you referred to a Data Table, so I wasn't sure.
I don't understand how you want the season filter to work if it isn't based on dates. You said that selecting spring 23 should restrict the dates to Jan 1 2023 to Jun 30 2023. Where are those start and end dates coming from ?
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.