Forum Discussion

Niikk's avatar
Niikk
Frequent Visitor
1 year ago
Solved

Filter data based on values in columns

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Niikk's avatar
Niikk
Frequent Visitor
1 year ago

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

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"
)