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

[amitchandak]

Also, consider

////////Period 
Period Rank = RANKX(all('Date'),'Date'[Period Start date],,ASC,Dense)

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))
Last 12 Period Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Period Rank]>=min('Date'[Period Rank])-12 
				&& 'Date'[Period Rank]<=max('Date'[Period Rank])))

Last 8 period Sales =
Var _min = maxx(allselected('Date','Date'[Period Rank]) -8
Var _max = maxx(allselected('Date','Date'[Period Rank]) 
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Period Rank]>=_min && 'Date'[Period Rank]<=_max))

[Anonymous]

Hi!

Today is 27 July.

If MTD is: TOTALMTD([Sales],’Date’[Date])

How would you calculate LMTD to 27 June?

[Anonymous]

Hello,

Thank you for this explanation.
But how do I get the totals displayed?
I tried to do this with comparable days from my calendar table. When I remove the day to get the total per month or per year, the amount is not displayed.
You also have the same thing on your print screen. The total Last Year Sales is not displayed.
How can I display it ?

thank you in advance.

[spandy34]

  I am wanting to do the similar thing but looking at your data table,  I want period 1 to start from 11/04/2022 and for it to repeat until Period 13 through out the year and then back to Period 1 for the next year (see table below)  

In your date table I could not see how you had calculated the Period Column.

Would you be able to help me add an extra Period Column in my calendar table which looks at the date and then inserts the correct Period based on the Start Date of the Date table being 11/04/22.  Im just really wondering if you could tell me what DAX I would put in the column please.

Many thanks

[Anonymous]

@amitchandak @spandy34 
I am using non standard date table and my date table  is below with the start date and end date of each period.
So i wanted to calculate MTD, MTD LY YTD, YTD LY, Yesterday, Yesterday LY, Today, Today LY.
Time intelligence functions are not working here. So confused a lot and stucked, Please i need help.