Forum Discussion

teetoes's avatar
teetoes
Frequent Visitor
3 years ago

Value not returning all selected in DAX formula

I'm hoping someone sees something obvious that I'm just missing here. I have a formula that I want to use to create a column that gives the total from the prior inception date year. I have a slicer in my report and when nothing is selected, it works as expected and adds all the prior years, subtracts the current year value and gives a total. Great, that's perfect. If I select a single year, it gives me exactly what I need. The issue comes when I try selecting multiple years. It seems to only be pulling some of the selections, and leaving one out, so the total is off just a wee bit. For example, if I select years 2022, 2021, 2019 then it excludes the prioryearGWP for 2019 from the total. I'm not sure why this is occurring. I think my Var Period is where my issue lies, but I'm fresh out of ideas on what's missing.

 

Here's my dax:

Prior Year(s) Gross Written Premium = 
VAR SelectedYear =
SELECTEDVALUE('Table'[Inception Date].[Year] )
VAR CurrentYear =
MAX('Table'[Inception Date].[Year])
VAR PolNumCacTable =
CALCULATETABLE('Table', ALLEXCEPT('Table','Table'[Policy Number],'Table'[Agreement ID]))
VAR PriorYear =
MAXX (FILTER ( PolNumTable, 'Table'[Inception Date].[Year] = (CurrentYear - 1 )), 'Table'[Inception Date].[Year] )
VAR PriorYearGWP =
SUMX ( FILTER(PolNumCacTable, 'Table'[Inception Date].[Year] = PriorYear), 'Table'[Gross Written Premium Amount] )
VAR CurrentYrGWP =
SUMX ( FILTER(PolNumTable, 'Table'[Inception Date].[Year] = CurrentYear), 'Table'[Gross Written Premium Amount] )
VAR Period =
IF (
SelectedYear = (PriorYear + 1),
CALCULATE(PriorYearGWP, ALLSELECTED('Table'[Inception Date].[Year])),
CALCULATE(SUMX('Table', [Gross Written Premium Amount]) - CurrentYrGWP)
)

RETURN
{Period}

 

9 Replies

  • Hello teetoes,

     

    Can you please try this:

    Prior Year(s) Gross Written Premium =
    VAR SelectedYear =
        SELECTEDVALUE('Table'[Inception Date].[Year])
    VAR CurrentYear =
        MAX('Table'[Inception Date].[Year])
    VAR PolNumCacTable =
        CALCULATETABLE('Table', ALLEXCEPT('Table', 'Table'[Policy Number], 'Table'[Agreement ID]))
    VAR PriorYear =
        MAXX(FILTER(PolNumCacTable, 'Table'[Inception Date].[Year] = (CurrentYear - 1)), 'Table'[Inception Date].[Year])
    VAR PriorYearGWP =
        SUMX(FILTER(PolNumCacTable, 'Table'[Inception Date].[Year] = PriorYear), 'Table'[Gross Written Premium Amount])
    VAR CurrentYrGWP =
        SUMX(FILTER(PolNumCacTable, 'Table'[Inception Date].[Year] = CurrentYear), 'Table'[Gross Written Premium Amount])
    VAR Period =
        IF(
            SelectedYear = (PriorYear + 1),
            CALCULATE(SUMX('Table', [Gross Written Premium Amount]), ALLSELECTED('Table'[Inception Date].[Year])) - CurrentYrGWP,
            CALCULATE(SUMX('Table', [Gross Written Premium Amount]) - CurrentYrGWP)
        )
    RETURN
        {Period}
    • teetoes's avatar
      teetoes
      Frequent Visitor

      When I try that, it gives me 0.00 for any single selection and is not giving the correct total when multi-select, or giving the correct amount for anything selected 2020 and prior.

      • Sahir_Maharaj's avatar
        Sahir_Maharaj
        Icon for Super User rankSuper User

        Thank you for your feedback. Perhaps try:

        Prior Year(s) Gross Written Premium =
        VAR SelectedYear =
            SELECTEDVALUE('Table'[Inception Date].[Year])
        VAR CurrentYear =
            MAX('Table'[Inception Date].[Year])
        VAR PolNumCacTable =
            CALCULATETABLE('Table', ALLEXCEPT('Table', 'Table'[Policy Number], 'Table'[Agreement ID]))
        VAR PriorYear =
            CurrentYear - 1
        VAR PriorYearGWP =
            SUMX(FILTER(PolNumCacTable, 'Table'[Inception Date].[Year] = PriorYear), 'Table'[Gross Written Premium Amount])
        VAR CurrentYrGWP =
            SUMX(FILTER(PolNumCacTable, 'Table'[Inception Date].[Year] = CurrentYear), 'Table'[Gross Written Premium Amount])
        VAR Period =
            IF(
                SelectedYear = PriorYear,
                PriorYearGWP,
                CALCULATE(SUMX('Table', [Gross Written Premium Amount]) - CurrentYrGWP)
            )
        RETURN
            Period

         

    • teetoes's avatar
      teetoes
      Frequent Visitor

       

      Policy NumberYearGross WrittenPrior Year Gross Written
      1201750
      12020105
      120211510
      120182015
      120192520
      Total 7550

       

      For simplicity, here is a table I created to show policy number, gross written and prior year gross. What I would like is 3 potential results in the total row:

       

      Scenario 1: User selects no years from the slicer - we would get a value exactly as we see above, where gross for all years is totalled and then prior year totalled

       

      Scenario 2: User select single year. For example, if the user selects 2018, then gross written column would just show the total for policies from 2018, in this case it would be 20 and then for prior year it would show the total for 2017 which would be 5.

       

      Scenario 3: User selects multiple year. For example, they select 2020 and 2019, then we would see the prior year total of 2019 and 2018. So in this case gross would be 35 and prior year would be 45

       

      Is that helpful?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User

        Hi,

        You have shared the matrix (result) table.  Share a sample of the raw data in a format that can be pasted in an MS Excel file.