Lookup years and sum values based on selections in Power BI slicer

[Boyo]

Hello everyone,

I'm new to power BI, and I'm hoping someone can help me.

I' m trying to achieve the result table in the snapshot below using power BI DAX

I have a database with following data.

1. Column A: Sales Year - with years for historical and projected sales
2. Column B: Sales numbers for each sales team for each year
3. Column C: Names of each sales team

My Request

1. Create a new column in Power BI - with historical and projected years without repeating them i.e., Column F named "Fiscal Year".
2. Create a new column in Power BI that sum up the sales numbers in column G named "Yearly Sales Total" for the teams for each year in column F (Fiscal Year).

2.1. based on selected sales team using Power BI Slicer (sample in column F8:G10)

2.2. The DAX code will compare column named "Sales Year" to column named "Fiscal Year" to ensure the right sales numbers are returned for the teams in each year in column F.

I will appreciate it if someone could help me.

Many thanks

[v-tangjie-msft · Accepted solution]

Hi @Boyo ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) If you want to display data on a visual object, place the fields as shown in the figure below.

(3)If you want to create a new table, go to modeling->new table->Modify column name[Fiscal Year].

Table 2 = VALUES('Table'[Sales Year])

Then we can add a column which, by design, is not affected by the TEAM of Table 1, and create a measure if you want the total value to be filtered by the slicer. Measure values do not appear in the data view, they can only be rendered on the visual object.

Yearly Sales Total = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Sales Year]=EARLIER('Table 2'[Fiscal Year]) ))

Measure (Yearly Sales Total) = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Sales Year]=MAX('Table 2'[Fiscal Year]) && 'Table'[Sales Team] in VALUES('Table'[Sales Team])))

Measure = SUMX(SUMMARIZE('Table 2','Table 2'[Fiscal Year], "total",[Measure (Yearly Sales Total)]),[total])

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Boyo]

Solution accepted - Many thanks for your help.