Forum Discussion

markoB's avatar
markoB
New Member
1 year ago
Solved

Problem with DATESBETWEEN that has a variable inside

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... 🙂
datesbetween
DAX
Help
  • johnt75's avatar
    johnt75
    1 year ago

    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.

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      markoB
      New Member
      1 year ago

      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's avatar
        johnt75
        Super User
        1 year ago

        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 ?