Custom Predefined Date Ranges - Prior Year Calculations

[Anonymous]

I have used this post to create my custom predefined date ranges, which was really helpful btw.

https://community.powerbi.com/t5/Desktop/Custom-Predefined-Date-ranges/m-p/228667#M101979

Now I'm facing an issue when I try prior year calculations:

PY Sales =

    CALCULATE(

        SalesPerItem[NetSales]

        ,SAMEPERIODLASTYEAR(Calendar[Date])

    )

I understand that the issue is as @steph_io said:

"[...]it works to filter most measures, except those that is looking at prior period outside of the visual context.

Because the new DatePeriod table has already filtered the CreateDateTable, this formula will not go fetch the range of the prior year to give me a Year over Year value."

Does anybody know a solution or a workaround?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Prior year data is always filtered that is caused that there is a relationship between the Date Range table and Calendar table. For your requirement, we don't have to create any relationships among the Date Range table and others. We could use functions to implement filter. 

I created a simple sample that you can reference.

• Relationships

• Formulas

Custom data change = 
var sel = SELECTEDVALUE('Date Range'[Period])
var mon_today = MONTH(TODAY())
var year_today = YEAR(TODAY())
var mon = CALCULATE(SUM(SalesPerItem[ Sales]), FILTER('Calendar', 'Calendar'[MONTH] = mon_today))
var year = CALCULATE(SUM(SalesPerItem[ Sales]), FILTER('Calendar', 'Calendar'[YEAR] = year_today))
return
SWITCH(TRUE(), sel="This month",mon,sel="This year",year,SUM(SalesPerItem[ Sales]))

Last year = CALCULATE(SUM(SalesPerItem[ Sales]),SAMEPERIODLASTYEAR('Calendar'[Date]))

I attached my sample that you can try and check if is what you want. Hope it may help you.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.