Value not returning all selected in DAX formula

[teetoes]

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}

[Ashish_Mathur]

Hi,

Share some data and show the exact result you are expecting.

[teetoes]

Policy Number	Year	Gross Written	Prior Year Gross Written
1	2017	5	0
1	2020	10	5
1	2021	15	10
1	2018	20	15
1	2019	25	20
Total		75	50

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?

[mahenkj2]

Hi @teetoes 

In your sample data here, the last column is not prior year gross, but prior policy gross, right? Is it a measure or a recorded information already in data?

If you select slicer in 2018, gross written would be 20 and prior year gross would be of 2017 i.e. 5, but your last column is showing prior policy gross of 15. 

It may be like that, but first like to confirm with you.

Thanks.

[teetoes]

Prior year gross is a measure of the sum of gross from the prior year's written premium. Policies have more than one row in our db so we roll all of these amounts into one in the original query to get a single value for gross written premium. 

I see why the confusion. Policy number should all be 1 in this table to make the example work. Sorry about that.

[Ashish_Mathur]

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.

[Sahir_Maharaj]

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]

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]

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]

That gives the same resulting zeroes.