Calculate CY and PY Sales based on Year Selected

[Anonymous]

Hello All,

I have a pretty basic scenario for which I am unable to figure out how to approach the solution. I need to show the CY sales and PY sales based on Year Selected.

For example,

If I select Year = 2019 in a filter, then CY Should be 2019 and PY should be 2018

If I select Year = 2018, then CY should be 2018 and PY should be 2017.

Below is the image how sample data looks like

Sample Data

I hope some one helps me out in this scenario.

Thanks

[Anonymous]

Hello All,

[Anonymous]

I have a pretty basic scenario for which I am unable to figure out how to approach the solution. I need to show the CY sales and PY sales based on Year Selected.

[Anonymous]

For example,

[Anonymous]

If I select Year = 2019 in a filter, then CY Should be 2019 and PY should be 2018

[Anonymous]

If I select Year = 2018, then CY should be 2018 and PY should be 2017.

[Anonymous]

Below is the image how sample data looks like

[Anonymous]

Sample Data

[Anonymous]

I hope some one helps me out in this scenario.

[Anonymous]

Thanks

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a new table with this formula

Table = VALUES(Sheet2[Year])

Sheet2 is the table as your example data.

Don't connect this "Table" with Sheet2, it means the two tables should have no relationship.

Add Table[year] in the slicer, create measures in "Sheet2".

Selected year = SELECTEDVALUE('Table'[Year])\

CY = CALCULATE(SUM(Sheet2[Sales]),ALLSELECTED(Sheet2))

PY = 
CALCULATE(SUM(Sheet2[Sales]),FILTER(ALL(Sheet2),[Year]=[Selected year]-1))

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-juanli-msft 

Thanks for your help. I applied your solution and it's working perfectly with a minor chage to CY.

The CY measure 

CY = CALCULATE(SUM(Sheet2[Sales]),ALLSELECTED(Sheet2))

 is giving total sales of all years.  So, I changed it to 

CY = 
CALCULATE(SUM(Sheet2[Sales]),FILTER(ALL(Sheet2),Sheet2[Year]=Sheet2[Selected year]))

[Anonymous]

Hello @v-juanli-msft,

                  How can I calculate YTD Sales for CY and PY based on year selected?

Regards,

James Arthur

[v-juanli-msft]

Hi @Anonymous 

Does the previous measure not help?

How about this DAX function TOTALYTD which evaluates the year-to-date value of the expression in the current context?

Best Regards

Maggie