In PowerBI (DAX) how to total sum in the first and last year for only names that are present in all years

0
votes

Need help with Power BI DAX to be find total amount in the first year for only those names that are present in all the years.

Here is a sample table:

Name Year Amount

A 2015 100

B 2015 50

C 2015 150

A 2016 200

B 2016 150

C 2016 150

A 2017 300

C 2017 300

D 2017 250

A 2018 300

C 2018 300

D 2018 300

E 2018 500

In the above table A and C are present in all the years, and hence I want total amount for both A and C in the starting year

Result:

2015

250

How do I achieve this? The key is only for those names that are present consistently in the year range.

Thank you very much for your help in advance!

Regards, Naresh

1
countsumpowerbidaxfind-occurrences

1 Answers

0
votes

Please try the following. It loops over any Name value in the current context and then it counts the number of years for that Name and if it matches the total number of years it sums up the amount for that Name. 

SUMX(
 VALUES(Tbl[Name]),
 IF(
   CALCULATE(DISTINCTCOUNT(Tbl[Year]), ALLEXCEPT(Tbl, Tbl[Name]))
   = CALCULATE(DISTINCTCOUNT(Tbl[Year]), ALL(Tbl)),
   CALCULATE(SUM(Tbl[Amount]))
  )
 )

//tweaking your attempt by including ALLSELECTED() and not including Year in ALLEXCEPT
VAR FromYear = CALCULATE(MIN('Tbl'[Year]),ALLSELECTED())
VAR ToYear = CALCULATE(MAX('Tbl'[Year]), ALLSELECTED())
VAR CountYears = ToYear - FromYear + 1 
RETURN 
SUMX (
 VALUES ( 'Tbl'[Name] ), 
 IF ( 
  CALCULATE ( DISTINCTCOUNT ( 'Tbl'[Year] ), ALLEXCEPT ( 'Tbl', 'Tbl'[Name], 'Tbl'[Category] )) = CountYears, 
  CALCULATE(SUM('Tbl'[Amount]) , 'Tbl'[Year]=FromYear)
 )
)