Calculate YTD for Previous Year

[o59393]

Hi all

I need to calculate the YTD from PY, in this case from Jan 2021 to Aug 2021.

The table is the following:

Place	Period	Units
A	1/1/2021	19,652,771
A	2/1/2021	22,247,912
A	3/1/2021	25,950,207
A	4/1/2021	20,442,619
A	5/1/2021	18,639,247
A	6/1/2021	28,158,195
A	7/1/2021	22,852,969
A	8/1/2021	23,274,828
A	9/1/2021	28,972,763
A	10/1/2021	23,825,102
A	11/1/2021	25,176,135
A	12/1/2021	36,040,867
A	1/1/2022	24,884,169
A	2/1/2022	25,043,001
A	3/1/2022	30,881,156
A	4/1/2022	25,377,539
A	5/1/2022	25,415,291
A	6/1/2022	31,557,732
A	7/1/2022	24,007,911
A	8/1/2022	22,339,903

The solution would be:

The measures I have are:

Units YTD = calculate(SUM(Units),DATESYTD(Query1[Period)])

Units YTD PY= CALCULATE([Units YTD],SAMEPERIODLASTYEAR(Query1[Period]))

For the Units YTD PY measure, I am getting the full 2021 result which is incorrect.

How can I get it ok?

Thanks.

[lbendlin]

- in your calendar table add a column "IsPastPY"  that tags which months should be considered.  (test if the date is older that one year before today)

- add that tag as a filter. 

Units YTD PY= CALCULATE([Units YTD],SAMEPERIODLASTYEAR(Query1[Period]),IsPastPY=TRUE())

- make sure your calendar table is in import mode and is refreshed frequently (daily/)

[o59393]

Hi @lbendlin 

The datasource is a SQL file with thousands of rows that I can't edit and also contains all the data, no calendar table. Adding a column would add memory to the BI I think.

Is there any way to do it without adding columns?

Thanks!

[lbendlin]

Those are not valid reasons. Always have a calendar table in your data model.