Calculate YTD CY and PY Sales based on Year Selected

[Anonymous]

Hello Community,

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

So, here what I have done so far

Created a new table with this formula

Table = VALUES(Sheet2[Year])

Added Table[year] in the slicer, created the measures in "Sheet2".

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

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

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

Every thing is working fine.

Now I have to calculate YTD Sales for CY and PY based on year/month selected.

Thanks,

James Arthur

[Anonymous]

Hello Community,

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

So, here what I have done so far

[Anonymous]

Created a new table with this formula

Table = VALUES(Sheet2[Year])

Added Table[year] in the slicer, created the measures in "Sheet2".

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

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

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

Every thing is working fine.

Now I have to calculate YTD Sales for CY and PY based on year/month selected.

Thanks,

James Arthur

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous, 

For your requirement, you can use SAMEPERIODLASTYEAR() and TOTALYTD() function. According to your Sample data, I create measures: 

YTD for CY = TOTALYTD(SUM(Sheet2[Sales]),'Table'[Date])

YTD for PY = CALCULATE(SUM(Sheet2[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

Best Regards,
Qiuyun Yu