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

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 🙂


  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      This is almost identical to the solution I ended up with. I have my different date ranges in measures (one for start and one for stop date) and then I filter it through a period table slicer 🙂