Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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-
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- |
|
(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.
Solved! Go to 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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |