Forum Discussion

VTork's avatar
VTork
Frequent Visitor
3 years ago

Calculate SUM and filter by dates (between different tables)

Hi - I was wondering if someone could help me with this. I got 3 tables: F1, F2 and F3. Each of them got a date column and a value column. I'd like to create a measure that sum all the values in F1 that got the date 01/01/2023 together with all the values in F2 that got the date 01/01/2023 and all the values in F3 with the date 01/01/2023. I tried the below DAX formula, but can't get it right. Would anyone be able to help? 😊 

2023 = CALCULATE(SUM(F1[Y1 Value])+SUM(F2[Y2 Value])+SUM(F3[Y3 Value]),FILTER(ALL(F1,F1[TMS Revenue Y1]=DATE(2023, 01, 01), F2[Y2]=DATE(2023, 01, 01), F3[Y3]=DATE(2023, 01, 01))))

Planning to do this for each year up to 2033 and then create a line chart showing the total value for each year that can be filtered by other variables within the tables (it's only the columns above that differ between the 3 tables), so there's a relationship. However, not sure if this would work as the calendar is not aligned between the tables... 

Many thanks! 
Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    VTork Try:

    2023 Measure =
  VAR __Date = DATE(2023,1,1)
  VAR __F1 = SUMX(FILTER(ALL('F1'),[Date] = __Date),[Value])
  VAR __F2 = SUMX(FILTER(ALL('F2'),[Date] = __Date),[Value])
  VAR __F3 = SUMX(FILTER(ALL('F3'),[Date] = __Date),[Value])
  VAR __Result = __F1 + __F2 + __F3
RETURN
  __Result
    • VTork's avatar
      VTork
      Frequent Visitor
      3 years ago

      Hi Greg_Deckler - thank you for getting back to me so quickly! 
      Sorry I'm still quite new to Power Bi, tried to follow your guidance but didnt get it quite right....

      2023 Measure =
        VAR __Date = DATE(2023,1,1)
        VAR __F1 = SUMX(FILTER(ALL(F1[Y1].[Date] = __Date),F1[Y1 Value]),
        VAR __F2 = SUMX(FILTER(ALL(F2[Y2].[Date] = __Date),F2[Y2 Value]),
        VAR __F3 = SUMX(FILTER(ALL(F3[Y3].[Date] = __Date),F3[Y3 Value]),
        VAR __Result = __F1 + __F2 + __F3
      RETURN
      __Result ))

      Any advice here? 😇