Forum Discussion
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 i...
Sahir_Maharaj
Super User
Super UserHello 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}
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 PeriodThat gives the same resulting zeroes.