cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate CY and PY Sales based on Year Selected

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

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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
Not applicable

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
Not applicable

Hello @v-juanli-msft,

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

Regards,

James Arthur

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors