Add custom dates parameter in DAX query

LambaR · ‎02-07-2024

Hi,

I am interested in adding a custom date parameter, such as Start Date and End Date, to my DAX query. Currently, I am utilizing a Custom Date Range Slicer, offering options like "Last30Days," "Yesterday," "WTD," "MTD," and "YTD." However, I would like to introduce a "Custom Date" option, allowing users to select a specific Start Date and End Date tailored to their needs, similar to the Date Slicer but for a custom date range.

Here is the current structure of my tables:

Master
Calendar (manually created)
SelectedDateRange (manually created)

My approach so far:

Step 1: Calculate all the dates for "Last30Days," "Yesterday," "WTD," "MTD," and "YTD," creating a new table named SelectedDateRange. (Refer below codes)

Step 2: Create a Calendar table encompassing dates from '2022-01-01' to today()-1.

Step 3: Establish a relationship between SelectedDateRange and the Calendar table based on Dates (Cardinality: Many to One).

Step 4: Establish another relationship between the Calendar and Master tables based on Dates (Cardinality: One to Many).

Now, I am contemplating the addition of StartDate and EndDate as custom parameters, allowing users to select them in the SelectedDateRange table based on their preferences.

I would appreciate any suggestions or guidance on achieving this goal. If there is a more efficient or preferable way to accomplish the same objective, please share your insights based on your expertise.

SelectedDateRange Codes:

SelectedDateRange = 
VAR TodayDate = TODAY()-1
VAR YearStart = CALCULATE(STARTOFYEAR('Master'[Date]), YEAR('Master'[Date]) = YEAR(TODAY()) )
VAR MonthStart = CALCULATE(STARTOFMONTH('Master'[Date]), YEAR('Master'[Date]) = YEAR(TODAY()), MONTH('Master'[Date]) = MONTH(TODAY()) )
VAR DaysToMonday = TodayDate - (WEEKDAY(TodayDate, 2) - 1)
VAR Yesterday = TodayDate
VAR Last30Days = TodayDate - 30

VAR Result =
UNION(
    ADDCOLUMNS(
        CALENDAR(YearStart, TodayDate),
        "Selection", "YTD",
        "Id",5
    ),
    ADDCOLUMNS(
        CALENDAR(MonthStart, TodayDate),
        "Selection", "MTD",
        "Id",4
    ),
    ADDCOLUMNS(
        CALENDAR(DaysToMonday, TodayDate),
        "Selection", "WTD",
        "Id",3
    ),
    ADDCOLUMNS(
        CALENDAR(Yesterday, TodayDate),
        "Selection", "Yesterday",
        "Id",2
    ),
    ADDCOLUMNS(
        CALENDAR(Last30Days, TodayDate),
        "Selection", "Last30Days",
        "Id",1
    )
)

RETURN
Result

Relationship Snapshot :

Thank you.