Forum Discussion

Fragmaticx's avatar
Fragmaticx
Frequent Visitor
1 year ago
Solved

Filter 2 dates in the same table, at once

Hi  I have a flat table with ShipmentDateCost and ShipmentDateSales  I want to find a way to filter on those 2 dates at once. So I want to see all bought and solgt in ex. september 2024? ...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi Fragmaticx ,

    This maybe occur because the FORMAT function is returning string, let try this upadated DAX:

    IsInSelectedPeriod = 
VAR SelectedYearMonth = SELECTEDVALUE('DateTable'[ShipPeriod])
VAR SelectedYear = YEAR(DATEVALUE(SelectedYearMonth & "01"))
VAR SelectedMonth = MONTH(DATEVALUE(SelectedYearMonth & "01"))
RETURN 
    IF(
        CALCULATE(
            COUNTROWS('BaseData'),
            FILTER(
                'BaseData',
                YEAR('BaseData'[ShipmentDateSales]) = SelectedYear
                && MONTH('BaseData'[ShipmentDateSales]) = SelectedMonth
                || YEAR('BaseData'[ShipmentDateCost]) = SelectedYear
                && MONTH('BaseData'[ShipmentDateCost]) = SelectedMonth
            )
        ) > 0,
        1,
        0
    )
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi Fragmaticx ,

Instead of using two separate date tables, create a single calendar table. This table will be the reference for both ShipmentDateCost and ShipmentDateSales.

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)), 
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

Make sure to create a calendar table with your desired range

 

Create a measure to check if a record matches the selected YearMonth

IsInSelectedPeriod = 
VAR SelectedYearMonth = SELECTEDVALUE(Calendar[YearMonth])
RETURN 
    IF(
        FORMAT('YourFlatTable'[ShipmentDateCost], "YYYY-MM") = SelectedYearMonth 
        || FORMAT('YourFlatTable'[ShipmentDateSales], "YYYY-MM") = SelectedYearMonth,
        1,
        0
    )

 

Apply the IsInSelectedPeriod measure as a visual-level filter with a condition set to = 1. This ensures only relevant rows appear in your visuals.

  • Fragmaticx's avatar
    Fragmaticx
    Frequent Visitor
    1 year ago

    Hi   Bibiano_Geraldo

    I did what you suggested using this measure: 

    IsInSelectedPeriod = 
VAR SelectedYearMonth = SELECTEDVALUE('DateTable'[ShipPeriod])
RETURN 
    IF(
        CALCULATE(
            COUNTROWS('BaseData'),
            FILTER(
                'BaseData',
                FORMAT('BaseData'[ShipmentDateSales], "YYYYMM") = SelectedYearMonth
                || FORMAT('BaseData'[ShipmentDateCost], "YYYYMM") = SelectedYearMonth
            )
        ) > 0,
        1,
        0
    )


    If I join my date table on sales and filter october 24 i get a different amount than if i join the date table on my table and filter ostober using this logic.

     


    Did i miss something?

     

    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Icon for Super User rankSuper User
      1 year ago

      Hi Fragmaticx ,

      This maybe occur because the FORMAT function is returning string, let try this upadated DAX:

      IsInSelectedPeriod = 
VAR SelectedYearMonth = SELECTEDVALUE('DateTable'[ShipPeriod])
VAR SelectedYear = YEAR(DATEVALUE(SelectedYearMonth & "01"))
VAR SelectedMonth = MONTH(DATEVALUE(SelectedYearMonth & "01"))
RETURN 
    IF(
        CALCULATE(
            COUNTROWS('BaseData'),
            FILTER(
                'BaseData',
                YEAR('BaseData'[ShipmentDateSales]) = SelectedYear
                && MONTH('BaseData'[ShipmentDateSales]) = SelectedMonth
                || YEAR('BaseData'[ShipmentDateCost]) = SelectedYear
                && MONTH('BaseData'[ShipmentDateCost]) = SelectedMonth
            )
        ) > 0,
        1,
        0
    )