Calculate Same period last year sales using Custom Calender

[Anonymous]

hello All,

I am trying to calculate same period last year by using Custom calender.

My calender looks like below table.

Year   period         StartDate                    EndDate

2019	P01	30-12-2018 00:00:00	26-01-2019 00:00:00
2019	P02	27-01-2019 00:00:00	23-02-2019 00:00:00
2019	P03	24-02-2019 00:00:00	23-03-2019 00:00:00
2019	P04	24-03-2019 00:00:00	20-04-2019 00:00:00
2019	P05	21-04-2019 00:00:00	18-05-2019 00:00:00
2019	P06	19-05-2019 00:00:00	15-06-2019 00:00:00
2019	P07	16-06-2019 00:00:00	13-07-2019 00:00:00
2019	P08	14-07-2019 00:00:00	10-08-2019 00:00:00
2019	P09	11-08-2019 00:00:00	07-09-2019 00:00:00
2019	P10	08-09-2019 00:00:00	05-10-2019 00:00:00
2019	P11	06-10-2019 00:00:00	02-11-2019 00:00:00
2019	P12	03-11-2019 00:00:00	30-11-2019 00:00:00
2019	P13	01-12-2019 00:00:00	28-12-2019 00:00:00
2020	P01	29-12-2019 00:00:00	25-01-2020 00:00:00
2020	P02	26-01-2020 00:00:00	22-02-2020 00:00:00
2020	P03	23-02-2020 00:00:00	21-03-2020 00:00:00
2020	P04	22-03-2020 00:00:00	18-04-2020 00:00:00
2020	P05	19-04-2020 00:00:00	16-05-2020 00:00:00
2020	P06	17-05-2020 00:00:00	13-06-2020 00:00:00
2020	P07	14-06-2020 00:00:00	11-07-2020 00:00:00
2020	P08	12-07-2020 00:00:00	08-08-2020 00:00:00
2020	P09	09-08-2020 00:00:00	05-09-2020 00:00:00
2020	P10	06-09-2020 00:00:00	03-10-2020 00:00:00
2020	P11	04-10-2020 00:00:00	31-10-2020 00:00:00
2020	P12	01-11-2020 00:00:00	28-11-2020 00:00:00
2020	P13	29-11-2020 00:00:00	26-12-2020 00:00:00

As per the above table, every PERIOD is of 4 weeks, and one FY may have 13 periods.

I have sales data as below image.

for 2020, currently i have 4 periods of sales data only, but when i do the sameperiodlast year, then it will take whole sales sum of 2019 which is not fare.

so i want to compare what ever the periods sales values we have in current FY, those no of periods should be compared in every previous year.

Can anyone please. suggest me.

Thanks,

Mohan V.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

For a calculated column.

Column = CALCULATE(SUM(Sales[CY_S]),FILTER(ALLEXCEPT(Sales,Sales[F_P]),Sales[F_Y]=EARLIER(Sales[F_Y])-1))

For a measure.

Measure = CALCULATE(SUM(Sales[CY_S]),FILTER(ALLEXCEPT(Sales,Sales[F_P]),Sales[F_Y]=SELECTEDVALUE(Sales[F_Y])-1))

 

Best Regards,

Jay

[amitchandak]

@Anonymous , refer to my blog how can you use rank to work with such a period. In your case last year is 13 periods behind

Check this first how to use RANK

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

https://www.youtube.com/watch?v=7Jc3D4iaTqs

Then refer this how to work that in case of period

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739