Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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 DataSample Data
I hope some one helps me out in this scenario.
 
Thanks
 
 
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
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))

6.png

 

 

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.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
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))

6.png

 

 

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]))

Capture.PNG

Anonymous
Not applicable

Hello @v-juanli-msft,

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

 

Regards,

James Arthur

                        

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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