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.

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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