Power BI: Working with Non-Standard Time Periods

[amitchandak]

In my last article, I discussed how we can compare two date ranges. This time, we talk about non-standard periods.

Comparing a month to the last month or year to last year has been made easy by Power BI time intelligence functions. There are many ways you can achieve these results. But in this article, we take on a non-standard period comparison.

We have created a non-standard calendar, each with a period of 28 days and a year of 12 such periods. As part of the data modeling, we have enhanced this calendar to have an incremental period number and quarter no. So, basically, period now is the primary key for period-year. This will help us in easily achieving the desired comparison.

The calendar is loaded as Date table and also marked as the date table. We have also added the standard period (Month) and year in this calendar. This has been done to create a reference for what we achieve.

Other than, this we have sales data and we have joined the "sales date" with the date tables.

In the standard calendar, we can easily compare a month to last month and year to last year. Here we will try to do the same with our custom period and year.

Sample of data we have:

Relationship Diagram:

Standard Last month and Last year calculations:

LMTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
LY Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

What we want:

Year Vs Last Year and Custom Year Vs Custom Last Year

Month Vs Month and Period Vs Period

Custom period and year formula's we have used:

LastPeriod Sales = 
var _period =SELECTEDVALUE('Date'[Period No])
return
CALCULATE(sum(Sales[Sales Amount]),filter(ALL('Date'),'Date'[Period No]=(_period-1)))


Last Year Sales = 
var _period =SELECTEDVALUE('Date'[Year])
return
CALCULATE(sum(Sales[Sales Amount]),filter(ALL('Date'),'Date'[Year]=(_period-1)))

Pbix file is attached to this article.

There can be many other ways you can do it. We would like you to post the same in the comments.

In case you have any questions, queries, or suggestions do let us know in comments.

sales_non_std_cal.pbix