Forum Discussion
teetoes
3 years agoFrequent Visitor
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
3 years agoThank 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
3 years agoFrequent Visitor
That gives the same resulting zeroes.