Time Intelligence calculations based on Fiscal Calendar

Question

Hello Experts,&nbsp;I tried to search for fiscal time intelligence calculations to learn but did find any resources. Appreciate it if you could please help me with this?I have a fiscal calendar table and fact table. The fiscal Year starts in February. I&nbsp; want to create measures based on fiscal dates like Fiscal YTD, Fiscal MTD, Fiscal QTD, Fiscal Previous Year, Fiscal Previous Month,&nbsp;Fiscal Previous Quarter, and Last 7 fiscal days etc.Attached file.&nbsp;File link-&nbsp;pbix&nbsp;Thank you

lbendlin · Answer

You can use the built-in time intelligence functions for nearly all of this. Some functions allow you to specify the end of the fiscal year.
The only exception is the Fiscal Quarter stuff - that you have to do manually, but you can use EDATE() shifting to great effect.

sid_sandy · Answer

Hi&nbsp;lbendlin&nbsp;, I tried to use DATEADD to calculate Previous Fiscal Year as below but not time intelligence function like DATEADD, PREVIOUSYEAR etc&nbsp;PY Fiscal = var curyear=MAX(FY_dates[FISCAL_YEAR No])var preyear=curyear-1returnCALCULATE([Total Sales],FILTER(ALL(FY_dates),FY_dates[FISCAL_YEAR No]=preyear))Could you pls show your examples for these time intelligence calculations? I'd appreciate your help.Thank you so much!&nbsp;RegardsSid

amitchandak · Answer

Sid_Sandy , For Fiscal Year and month Datesytd and datesmtd should work.
Get required columns and qtr rank of feb calendar from here
Creating Financial Calendar - From Any Month https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
&nbsp;
example&nbsp;
&nbsp;
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
&nbsp;
..columns
Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"1/31"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)
&nbsp;
measures This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
&nbsp;
year
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
&nbsp;
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Quarter behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Month))
&nbsp;
Power BI — Year on Year with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38ahttps://www.youtube.com/watch?v=km41KfM_0uAPower BI — Qtr on Qtr with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839https://www.youtube.com/watch?v=8-TlVx7P0A0Power BI — Month on Month with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090ehttps://www.youtube.com/watch?v=6LUBbvcxtKAPower BI — Week on Week and WTD https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123https://www.youtube.com/watch?v=pnAesWxYgJ8
&nbsp;
&nbsp;

sid_sandy · Answer

Hi&nbsp;amitchandak&nbsp;, thanks for sharing great resources, will go through these. it'll help me a lot.just walked through the shared financial calendar for Feb. it doesn't match with my calendar which based on 445. Also just checked the date field in your shared calendar and it seems this fiscal dates but in my case its regular dates. I have to use the that calendar which I've shared. The reason is I think&nbsp; I'm not getting different values when I use the regular date field in time intelligence function.Just a help here, do I need to create a fiscal dates field to use the time intelligence function?I have fiscal year, fiscal month, fiscal qtr, fiscal weeks but not fiscal days. can I consider the days from regular date filed and create a fiscal dates using fiscal year, fiscal month and days from regular date field?&nbsp;I have created some columns in power query to achieve my goals but still I'm missing somthing.Here is M code, appreciate if you have a look and help me.&nbsp;letSource = Excel.Workbook(File.Contents("C:\Users\123\Downloads\Fiscal_Calendar.xlsx"), null, true),FY_dates_Sheet = Source{[Item="FY_dates",Kind="Sheet"]}[Data],#"Promoted Headers" = Table.PromoteHeaders(FY_dates_Sheet, [PromoteAllScalars=true]),#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"START_DATE", type date}, {"END_DATE", type date}, {"FISCAL_WEEK", type text}, {"FISCAL_MONTH", type text}, {"FISCAL_QUARTER", type text}, {"FISCAL_YEAR", Int64.Type}}),&nbsp; &nbsp; #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"START_DATE", Int64.Type}, {"END_DATE", Int64.Type}}),&nbsp; &nbsp; #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each {[START_DATE]..[END_DATE]}),&nbsp; &nbsp; #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),&nbsp; &nbsp; #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"START_DATE", type date}, {"END_DATE", type date}, {"Date", type date}}),&nbsp; &nbsp; #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Date", "START_DATE", "END_DATE", "FISCAL_WEEK", "FISCAL_MONTH", "FISCAL_QUARTER", "FISCAL_YEAR"}),&nbsp; &nbsp; #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "FY.1", each Text.Start([FISCAL_MONTH],4)),&nbsp; &nbsp; #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"FISCAL_YEAR", "FISCAL_YEAR No"}}),&nbsp; &nbsp; #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"FY.1", "FISCAL_YEAR"}}),&nbsp; &nbsp; #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "FQ", each Text.End([FISCAL_QUARTER],2),type text),&nbsp; &nbsp; #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Fiscal Month No", each Text.End([FISCAL_MONTH],2)),&nbsp; &nbsp; #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"Fiscal Month No", Int64.Type}}),&nbsp; &nbsp; #"Added Custom4" = Table.AddColumn(#"Changed Type5", "FW Number", each if Text.Length([FISCAL_WEEK]) = 7 then Text.End([FISCAL_WEEK],2) else Text.End([FISCAL_WEEK],1)),&nbsp; &nbsp; #"Changed Type6" = Table.TransformColumnTypes(#"Added Custom4",{{"FW Number", Int64.Type}}),&nbsp; &nbsp; #"Added Custom6" = Table.AddColumn(#"Changed Type6", "Calender DaysNo", each Date.Day([Date])),#"Added Custom5" = Table.AddColumn(#"Added Custom6", "Fiscal Month No Helper", each if [Fiscal Month No] = 12 then 1 else [Fiscal Month No] +1),#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom5",{{"Fiscal Month No Helper", Int64.Type}}),#"Added Custom7" = Table.AddColumn(#"Changed Type3", "Fiscal Month Name", each Date.ToText(#date(1,[Fiscal Month No Helper],1),"MMM"), type text),#"Added Custom8" = Table.AddColumn(#"Added Custom7", "F_Quarter", each Text.End([FQ],1)),#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom8",{{"F_Quarter", Int64.Type}}),#"Added Custom9" = Table.AddColumn(#"Changed Type4", "F_Mo_Period", each Text.End([FISCAL_MONTH],3),type text)in#"Added Custom9"&nbsp;&nbsp;Thanks a lot again!RegardsSid

lbendlin · Answer

What's the structure of your fiscal year? When does it start?

Forum Discussion

Time Intelligence calculations based on Fiscal Calendar

8 Replies

Related content

Interesting effects with calendar-based time intelligence

Calendar-based Time Intelligence Preview - Issue

Calendar table and time intelligence

Calendar Slicer with Different Business Calendars

Help with Date intelligence