Forum Discussion

HB13's avatar
HB13
Helper I
4 years ago
Solved

How to adjust DAX measure to include multiple values?

Hi PBI gang

I have this measure;

var Year_ = SELECTEDVALUE(_DateTable[Years])
return
CALCULATE(SUM(Table1[PremiumVAT]), Table2[InceptionDate].[Year] = Year_)
+
CALCULATE(SUM(Table1[PremiumExclVAT]), Table2[InceptionDate].[Year] = Year_)



If my date filter is set with a Date range of the same year (i.e only 2021) then the measure works but if the range is across two years (2020, 2021) then it doesnt work. What do I need to change in order have more than one year value incorporated into the measure?

Many thanks!
Need Help
Tips and Tricks
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi HB13 ,

     

    Please try this:-

    VAR max_Year_ =
    MAX ( _DateTable[Years] )
VAR min_Year_ =
    MIN ( _DateTable[Years] )
RETURN
    CALCULATE (
        SUM ( Table1[PremiumVAT] ),
        FILTER (
            Table2,
            Table2[InceptionDate].[Year] <= max_Year_
                && Table2[InceptionDate].[Year] >= min_Year_
        )
    )
        + CALCULATE (
            SUM ( Table1[PremiumExclVAT] ),
            FILTER (
                Table2,
                Table2[InceptionDate].[Year] <= max_Year_
                    && Table2[InceptionDate].[Year] >= min_Year_
            )
        )

     

    BR,

    Samarth

     

     

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi HB13 ,

     

    Please try this:-

    VAR max_Year_ =
    MAX ( _DateTable[Years] )
VAR min_Year_ =
    MIN ( _DateTable[Years] )
RETURN
    CALCULATE (
        SUM ( Table1[PremiumVAT] ),
        FILTER (
            Table2,
            Table2[InceptionDate].[Year] <= max_Year_
                && Table2[InceptionDate].[Year] >= min_Year_
        )
    )
        + CALCULATE (
            SUM ( Table1[PremiumExclVAT] ),
            FILTER (
                Table2,
                Table2[InceptionDate].[Year] <= max_Year_
                    && Table2[InceptionDate].[Year] >= min_Year_
            )
        )

     

    BR,

    Samarth

     

     

    • HB13's avatar
      HB13
      Helper I
      4 years ago

      Hi Samarth_18 

      Thank you for your response.
      This helped a ton, although I had to adjust the max_ & min_Year variables so that it referenced what the the Date filter was selecting.