Forum Discussion

Sid_Sandy's avatar
Sid_Sandy
Regular Visitor
5 years ago

Time Intelligence calculations based on Fiscal Calendar

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 tabl...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

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's avatar
Sid_Sandy
Regular Visitor
5 years ago

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
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

     

    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-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

     

     

    • Sid_Sandy's avatar
      Sid_Sandy
      Regular Visitor
      5 years ago

      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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

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

    • Sid_Sandy's avatar
      Sid_Sandy
      Regular Visitor
      5 years ago

      it's 445 calendar and it starts from Feb.