Filter data based on values in columns

[Niikk]

Hi!
I have a Datetable which gets the lowest and highest datevalue from another table with financial data and then creates one row per unique date in the given MIN/MAX span. Amongst other columns I have calculated columns, for example Month-to-Date, that gives the value "MTD" in the column named MTD (so all dates in the current month has the value "MTD"). There is at the moment columns for QTD and YTD aswell. MTD calulated column looks like this:

MTD =
IF( YEAR(TODAY()) = DateLedger[Year] && MONTH(TODAY()) = DateLedger[Month Number] && TODAY() >= DateLedger[Date],"MTD")

Also have measures (in case those are easier to use then refering to column values), MTD as example:

DateMsr_MTD =

    DATESBETWEEN(

        'DateLedger'[Date],  

        MIN(DateLedger[MTD]),  

        MAX(DateLedger[MTD])

    )

My goal is to have a slicer with certain predefined datespans such as MTD, YTD etc. I don't want to calculate anything, I simply want predefined datespans that updates automatically once we hit a new month etc. I've tried building it with measures and also parameters hoping to easily have mutiple timespans that the users can click on instead of manually selecting desired dates. The only thing I managed to get working is to take one fo the calculated columns and draging it to a slicer, which then filters the data as it should. 

Note: I don't have direct access to the data, as in I cannot in any way open the power query so it must be solved with DAX or basic Power BI Desktop functionality. This is probably dead easy to solve, however after way to many hours of failure I'm hoping someone in here can help me 🙂

Thanks in advance!

[Kedar_Pande]

@Niikk 

Add a Calculated Column for Date Spans

DateSpan =
SWITCH(
TRUE(),
YEAR(TODAY()) = DateLedger[Year] && MONTH(TODAY()) = DateLedger[Month Number] && TODAY() >= DateLedger[Date], "MTD",
YEAR(TODAY()) = DateLedger[Year] && QUARTER(TODAY()) = DateLedger[Quarter] && TODAY() >= DateLedger[Date], "QTD",
YEAR(TODAY()) = DateLedger[Year] && TODAY() >= DateLedger[Date], "YTD",
"Other"
)

Add the DateSpan column to your slicer.
The slicer will display "MTD," "QTD," "YTD," and "Other." Users can select one of these spans.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Niikk]

Just for fun I tested to build one table per time period so each day in that timeframe gets one row in a table. For example D_MTD gives me 19 rows for the 19 dates in September, the table DAX looks like this:
D_MTD =
CALCULATETABLE(
SELECTCOLUMNS(DateLedger, "Dates", [Date], "Period", "MTD", "SortOrder", "1"),
YEAR(TODAY()) = DateLedger[Year], MONTH(TODAY()) = DateLedger[Month Number], TODAY() >= DateLedger[Date]
)

Then did one for QTD aswell and then got them both together as one table by a simple Union. I then setup a relationship from the combined dates table to DateLedger and now it works as it should! But, now the date hierarchy is lost instead from DateLedger which breaks many of the existing reports. Is the best way forward to have D_Span (the union table) set hierarchy in graphs etc or is there a way to manually build the hierarchy back to DateLedger? 

Any solution forwards is appreciated, as long as I get static datespans that can be easily selected for the users and datefiltering from financial data still works 🙂

[v-rzhou-msft]

Hi @Niikk ,

As far as I know, if you create a calculated column, it couldn't show multiple results YTD/MTD/QTD at the same time.

Here I suggest you to create a period table for slicer and then create a measure to filter the table visual.

Selection = 
DATATABLE(
    "Selection",STRING,
    "Order",INTEGER,
    {
        {"MTD",1},
        {"QTD",2},
        {"YTD",3}
    })

Measure:

Filter Measure = 
SWITCH (
    SELECTEDVALUE ( Selection[Order] ),
    1,
        IF (
            YEAR ( TODAY () ) = MAX ( DateLedger[Year] )
                && MONTH ( TODAY () ) = MAX ( DateLedger[Month Number] ),
            1,
            0
        ),
    2,
        IF (
            YEAR ( TODAY () ) = MAX ( DateLedger[Year] )
                && QUARTER ( TODAY () ) = MAX ( DateLedger[Quarter Number] ),
            1,
            0
        ),
    3, IF ( YEAR ( TODAY () ) = MAX ( DateLedger[Year] ), 1, 0 ),
    1
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bibiano_Geraldo]

Hi @Niikk ,

Create a new calculated column in DateLedger table using this DAX:

Date Span = 
SWITCH(
    TRUE(),
    YEAR(TODAY()) = DateLedger[Year] && MONTH(TODAY()) = DateLedger[Month Number], "MTD",
    YEAR(TODAY()) = DateLedger[Year], "YTD",
    QUARTER(TODAY()) = DateLedger[Quarter], "QTD",
    "All Time"
)

Use this column directly in the slicer. It will dynamically update based on today’s date.

[Niikk]

Thanks for the fast reply! This works great for MTD, but not the other options since if I filter on YTD I get data for 2024-01-01 up to 2024-09-30, since 2024-10-01 to 2024-11-30 are marked QTD and december is marked YTD.

[Bibiano_Geraldo]

Thank you, i just made some changes here, please try this updated DAX:

Date Span = 
VAR TodayDate = TODAY()
VAR CurrentYear = YEAR(TodayDate)
VAR CurrentMonth = MONTH(TodayDate)
VAR CurrentQuarter = QUARTER(TodayDate)
VAR CurrentDay = DAY(TodayDate)

RETURN
SWITCH(
    TRUE(),
    -- MTD: Current month, from the first of the month to today
    YEAR(TodayDate) = DateLedger[Year] && MONTH(TodayDate) = DateLedger[Month Number], "MTD",
    
    -- YTD: All dates from the beginning of the current year to today
    YEAR(TodayDate) = DateLedger[Year] && DateLedger[Date] <= TodayDate, "YTD",
    
    -- QTD: All dates in the current quarter, from the first day of the quarter to today
    YEAR(TodayDate) = DateLedger[Year] && QUARTER(TodayDate) = DateLedger[Quarter] && DateLedger[Date] <= TodayDate, "QTD",
    
    -- All Time: Any date that doesn't fall into MTD, YTD, or QTD
    "All Time"
)