Time Intelligence calculations based on Fiscal Calendar

[Sid_Sandy]

Hello Experts,

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  want to create measures based on fiscal dates like Fiscal YTD, Fiscal MTD, Fiscal QTD, Fiscal Previous Year, Fiscal Previous Month, Fiscal Previous Quarter, and Last 7 fiscal days etc.

Attached file. 

File link- pbix 

Thank you

[lbendlin]

You would do things like 

TOTALYTD(something something,"1/31") 

to indicate the shift in the fiscal calendar vs the regular one.

This works for Month and Year time intelligence.  For Quarter intelligence you can use DATEADD(dates,1,MONTH) or EDATE(1)  depending on if you are shifting an entire table column or a single value.

Your 445 logic is independent of that, you will have to maintain that in your dates table manually. Don't waste your time with the M code, do it in the data source.

[Sid_Sandy]

Hi Experts,

I am still stuck on this 😕. I tried my efforts to figure it out but I'm not able to use time intelligence DAX functions using my calendar shared in pbix.

I really appreciate it if anyone can help me.

Thank you,

Regards

Sid

[lbendlin]

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]

Hi @lbendlin , I tried to use DATEADD to calculate Previous Fiscal Year as below but not time intelligence function like DATEADD, PREVIOUSYEAR etc

PY Fiscal =

var curyear=MAX(FY_dates[FISCAL_YEAR No])

var preyear=curyear-1

return

CALCULATE([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!

Regards

Sid

[lbendlin]

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

[Sid_Sandy]

it's 445 calendar and it starts from Feb. 

[amitchandak]

@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-Calend...

example 

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]))

..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)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

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))

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"))

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))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Sid_Sandy]

Hi @amitchandak , 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  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?

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.

let
Source = 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}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"START_DATE", Int64.Type}, {"END_DATE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each {[START_DATE]..[END_DATE]}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"START_DATE", type date}, {"END_DATE", type date}, {"Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Date", "START_DATE", "END_DATE", "FISCAL_WEEK", "FISCAL_MONTH", "FISCAL_QUARTER", "FISCAL_YEAR"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "FY.1", each Text.Start([FISCAL_MONTH],4)),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"FISCAL_YEAR", "FISCAL_YEAR No"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"FY.1", "FISCAL_YEAR"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "FQ", each Text.End([FISCAL_QUARTER],2),type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Fiscal Month No", each Text.End([FISCAL_MONTH],2)),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"Fiscal Month No", Int64.Type}}),
    #"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)),
    #"Changed Type6" = Table.TransformColumnTypes(#"Added Custom4",{{"FW Number", Int64.Type}}),
    #"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"

Thanks a lot again!

Regards

Sid