Problem with DATESBETWEEN that has a variable inside

[markoB]

Hi DAX gurus,

I'm having some troubles making DAX run as intended, i've spent a couple of days of testing quite a few things and can't get it to work, worst part is that separately (debbuging) everything seems to work correctly.

Dataset:
I have 3 datasets that are being used.
A MasterDatabase that has all the dimensions and measures, a DataTable that is generated our of MasterDatabase (and other, irrelevant datasets) that is linked with a 1 : * with MDB, and a Seasonality/liability dataset that is generated from MasterDB with a 1 : * connection and has a additional hierarchy columns 
Dashboard:
Slicer based on date from the Datatable (linked to MasterDB), slicer based on Season from the seasonality dataset, table in which i have dimensions and calculated measures from MasterDatabase
Intention:
I would like for the user to be able to select the period range they want as the basis (ie: sales from 1st of Jan up to the 18th Aug).
At the same time i need to have a measure that will hardcap the starting or ending dates based on the selected season. But let's do this part in two steps, i first need it to cap and not extend up to the max slicer range, if we get that going, in that case i will add a MIN(MAXX()) that will either take the hardcaped date or an earlier date if the slicer range comes up before that. (and MAX(MINX()) for the other way around.
DAX:
CapedMeasure = 

VAR StartDate =

SWITCH(

            TRUE(),

            SELECTEDVALUE(LiabilityHCTable[Simplified Liability]) = "S22", DATE(2022, 01, 01),

            SELECTEDVALUE(LiabilityHCTable[Simplified Liability]) = "S23", DATE(2023, 01, 01),

            SELECTEDVALUE(LiabilityHCTable[Simplified Liability]) = "F22", DATE(2022, 07, 01)           

)

VAR EndDate =  SWITCH(

            TRUE(),

            SELECTEDVALUE(LiabilityHCTable[Simplified Liability]) = "S22", DATE(2022, 06, 30),

            SELECTEDVALUE(LiabilityHCTable[Simplified Liability]) = "S23", DATE(2023, 06, 30),

            SELECTEDVALUE(LiabilityHCTable[Simplified Liability]) = "F22", DATE(2022, 12, 31)           

)

RETURN

    CALCULATE(

    SUM(MasterData[*Sales LC]),

    KEEPFILTERS(

        DATESBETWEEN(

            DateTable[Date],

            StartDate,

            EndDate

        )

    )

Problem:
The Dax works, but i don't get the intended result; let's suppose i selected S23 (spring 23), my measure should take all the sales from the 1st jan 23 up to the 30. june 23, but if in my slicer i go above that it will not cap at the 30th june mark and will take the max selected value from the slicer and sales from that whole range
If instead of the variables i use DATE() with the same value, it works as intended, it's the combination under calcualte that seems to be the problem.
I've tried 

SUMX(MasterData, MasterData[*Sales LC]),

    ALL(DateTable[Date]),

        DATESBETWEEN()))
but to no avail (keeps extended range).
and i've also tried:

    CALCULATE(

    SUM(MasterData[*Sales LC]),

    KEEPFILTERS(

        DATESBETWEEN(

            DateTable[Date],

            StartDate,

            EndDate

        )

    ),

    REMOVEFILTERS(DateTable[Date]),
and:

CALCULATE(

    SUM(MasterData[*Sales LC]),

      ALL(DateTable[Date]),

    FILTER(

        DateTable,

        DateTable[Date] >= StartDate &&

        DateTable[Date] <= EndDate 

    )
but both return blank.
Quite a few other solutions too (disconnected datetable as slicer selector, splitting the date var. in year, month, day, and injeting it as such in DATE() bellow the datesbetween etc), but the result ends up either blank or the whole selected slicer range.
If anybody has an idea on how to make the above work, i'm all ears... 🙂

[johnt75]

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.

[markoB]

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.

[johnt75]

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 ?

[markoB]

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 🙂

[johnt75 · Accepted solution]

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.

[markoB]

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!