Calcualte Sameperiod last year sales using CUSTOM CALENDER

[Anonymous]

Hello All,

I am trying to calculate sameperiod last year sales values by using CUSTOM CALENDER.

My calender looks like as below 

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

For year, every 4 weeks considered as A PERIOD. with that, one FY may have 13 periods. Now, i have sales data for 2020 till 4th period only as below image.

Now i would like to calculate previous year sales values for 4 periods only, so that i can compare with current year sales that is for 4 periods only.

like wise for every FY, i need to calcualte the sales values based on the number of periods in current year.

i found a blog with what i need but it is for standard calender year.

https://www.goodly.co.in/calculate-sameperiodlastyear-partial-year/

Can any one please guide me.

Thanks,

Mohan V.

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[MFelix · Accepted solution]

Hi @Anonymous ,

Believe that you need to build a calendar table with the start and end dates and also the periods.

Using this post and making some adjustments I have created a full calendar table with the Periods using as base your table. Check all the steps in the query after ==========

Now create a relationship between the calendar table and the sales then add the following measure:

PY = 
VAR currentYearMaxPeriod =
    CALCULATE (
        MAX ( 'Calendar'[Period] );
        FILTER ( 'Calendar'; 'Calendar'[Date] = MAX ( Sales[Date] ) )
    )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] );
        FILTER (
            ALLSELECTED ( 'Calendar' );
            'Calendar'[Year]
                = MAX ( 'Calendar'[Year] ) - 1
                && 'Calendar'[Period] >= MIN ( 'Calendar'[Period] )
                && 'Calendar'[Period] <= currentYearMaxPeriod
        )
    )

Check PBIX file attach.

In my example I have sales until July but as you can see if you change the filter the dates changes.