Fiscal Year Calculation and Report Filters

[Anonymous]

Hi Everyone,

I am relatively very new to Power BI as my organization is migrating from some other reporting tool to Power BI. I am facing multiple difficulties with Calendar Table and Fiscal Year calculations.

The Fiscal year for my company- May to April.

Calendar Period	Fiscal Year
May 2019 to April 2020 >	FY 2020
May 2020 to April 2021 >	FY 2021

Quarters-
May-July > Q1

Aug-Oct > Q2

Nov-Jan > Q3

Feb-Apr > Q4

Also, some tables have future date as well (for ex., product receive date, ETA, etc.) so I have to decide start and End Date (any suggestions on the best practice is appreciated)

I am struggling a lot with the Fiscal Dates and Periods.

I need to develop multiple reports. Most of my report has either of two requirements for filtering the data. Please see below-

1. To show data according to MTD, QTD, YTD or Date Range. Please find the below screenshot for reference-

Score Card                                                             01/10/2020-26/10/2020 (Report Heading)

Date Range

MTD

QTD

YTD

(Date Range, MTD, QTD and YTD are buttons)

Requirement-

If I select Date Range, it should allow me to edit the Date Range in the upper right corner or a pop-up should appear where I can select my Start and End Date and the selected date should be displayed in the top right corner (as shown).

If I select anything from MTD/QTD/YTD (As per Fiscal Year), the date should be displayed in the top right corner (As shown in the screenshot above)

(I've tried MTD, QTD and YTD functions but it did not work for me as I need to apply multiple slicers)

Let’s say today’s date is Oct 26, 2020

MTD should display Date as- Oct 1, 2020 to Oct 26, 2020

QTD should display Date as- Aug 1, 2020 to Oct 26, 2020

YTD should display Date as- May 1, 2020 to Oct 26, 2020

The data should get filtered/sliced as per the selected period.

OR

2. Monthly or Quarterly Data. Please find the below screenshot for reference-

Monthly

Quarterly

(Two buttons for Monthly and Quarterly)

Please find the below screenshot-

Select Year-

FY 2021

(A dropdown to select the Fiscal Year)

If I select Monthly, then it should allow me to select the Fiscal Year and then as per the selection, it should display the data for the selected and previous Fiscal Year.

Please note that- It should only display the data until Previous Month (Complete Month)

Period	Sales	Period End Date	Other Measure
May-19	1000	2018-05-31	10000
Jun-19	1000	2018-06-30	10000
Jul-19	2000	2018-07-31	10000
Aug-19	2000	2018-08-31	10000
Sep-19	1000	2018-09-30	10000
Oct-19	1700	2018-10-31	10000
Nov-19	1800	2018-11-30	10000
Dec-19	1900	2018-12-31	10000
Jan-20	2000	2019-01-31	10000
Feb-20	2100	2019-02-28	10000
Mar-20	2200	2019-03-31	10000
Apr-20	2300	2019-04-30	10000
May-20	2400	2019-05-31	10000
Jun-20	2500	2019-06-30	10000
Jul-20	2600	2019-07-31	10000
Aug-20	2700	2019-08-31	10000
Sep-20	2800	2019-09-30	10000

If I select Quarterly, it should display data for Prior Fiscal Year (Q1, Q2, Q3 and Q4) and Current Fiscal Year’s completed Quarter- (If we consider today’s date Oct 26, 2020 it should display Q1 2021 only because Q2 2021 is yet to be completed)

Period	Sales	Period End Date	Other Measure
Q1 2020	1000	2019-07-31	10000
Q2 2020	1000	2019-10-31	10000
Q3 2020	2000	2020-01-31	10000
Q4 2020	2000	2020-04-30	10000
Q1 2021	1000	2020-07-31	10000

Please help. I highly appreciate any help from the experts.

Thanks.

[amitchandak · Accepted solution]

@Anonymous , for MTD and YTD you can use time intelligence with date calendar

Get Calendar -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

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"04/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"04/30"))

For QTD - Add these columns in your date table

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

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

This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

Last year Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-4))

Last year QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-4 && [Qtr Day] <=max([Qtr Day])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.