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?

I tried adding 2 date tables, one for each field, and then a Year-Month table to filter those 2, but it does not work.


ChatGPT said something like this:

IsInSelectedMonth =
VAR SelectedMonth = SELECTEDVALUE('DateTableCost'[Month])
VAR SelectedYear = SELECTEDVALUE('DateTableCost'[Year])
RETURN
    IF(
        CALCULATE(
            COUNTROWS('BaseData'),
            YEAR('BaseData'[ShipmentDateCost]) = SelectedYear &&
            MONTH('BaseData'[ShipmentDateCost]) = SelectedMonth
        ) > 0 ||
        CALCULATE(
            COUNTROWS('BaseData'),
            YEAR('BaseData'[ShipmentDateSales]) = SelectedYear &&
            MONTH('BaseData'[ShipmentDateSales]) = SelectedMonth
        ) > 0,
        1,
        0
    )

but i dont think its giving me the proper result.

And ideas?

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
    )

8 Replies

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

    Hi Fragmaticx 

    You will need just one dates table with relationships to your fact table - one active and one inactive.

    Assuming active is on ShiptmentDateCost then the measure would be as simple as SUM (facttable[column]).  For ShipmentDateSales, that would be

    CALCULATE (
    SUM ( facttable[column] ),
    USERELATIONSHIP ( datetable[date], facttable[ShipmentDateSales] ) --USERELATIONSHIP to invoke an inactive relationshipo
)

     

  • 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
    )
  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Your approach with the 2 date tables and one date-month table on top doesn't work because it only filters to values where BOTH ShipmentDateCost and ShipmentDateSales were in the same month. And I think you want to see all the values which have been ShipmentDateCost OR ShipmentDateSales have been e.g. in september?

    • Fragmaticx's avatar
      Fragmaticx
      Frequent Visitor
      1 year ago

      Hi 

      yes I want to see either cost or sales in a given month.

      But I would like to be able to filter on table/graphs and so on 🙂 

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Fragmaticx 

    Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

     

    Best Regards

    Yongkang Hua