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.

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

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