Day of Fiscal Calendar

[DanielWoolcott]

Searched through the board posts, but did not find what I was looking for, so decided to ask.

We are reporting on multiple data points that we need to look and compare against the previous month.  We are using a 4/4/5 fiscal calendar though for the totals.  If it is the 14th day in the discal calendar for June, I need to do the same total for the 14th day of the previous fiscal month, May in this example.

I was thinking I had to add a "Fiscal Calendar Day" to my calendar, but have not done this yet.  I already have fiscal month, quarter and year, but if I am thinking this through right, none of those are granular enough to get what I need.  But I am also stuck how to add the fiscal day of month as well.

Looking for any suggestions.  Thank you in advance!

[amitchandak · Accepted solution]

@DanielWoolcott , If you already created 4,4,5 calendar then you can use rank to get current period vs previous period

Period Rank = RANKX(ALL('Date'),'Date'[Period Start ],,ASC,Dense)

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

Refer : https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[DanielWoolcott]

@amitchandak Thank you for the quick responce.  I am trying to walk my way through what you responded with.

Below is an example of our current calendar setup:

Calendar_Date	DayOfWeek	DayOfYear	DayofQuarter	WeekOfYear	PeriodOfYear	Qtr	FiscalYear	FiscalWeek	FiscalPeriod	FiscalQuarter
6/15/2020 0:00	2	170	79	25	6	2	2020	2020-25	2020-06	2020-02
6/14/2020 0:00	1	169	78	25	6	2	2020	2020-25	2020-06	2020-02
6/13/2020 0:00	7	168	77	24	6	2	2020	2020-24	2020-06	2020-02
6/12/2020 0:00	6	167	76	24	6	2	2020	2020-24	2020-06	2020-02
6/11/2020 0:00	5	166	75	24	6	2	2020	2020-24	2020-06	2020-02

And below is an example of what our sales data would look like:

Shiphead Invoice Date	Extended Amount
6/11/2020	$201,699
6/12/2020	$912,505
6/13/2020	$1,187,923
6/13/2020	$6,331,613
6/14/2020	$664,177
6/14/2020	$5,029,566
6/14/2020	$2,862,260

We want to be able to take the data above, and total it for the current fiscal month (not calendar month) and compare it to the same number of days from the previous fiscal month.

Today, being the 15th of June is actually the 23rd day of our fiscal month (Started on May 24th).  We need to compare to the first 23 days of the previous fiscal month (April 26th - May 18th)

[amitchandak]

@DanielWoolcott , if you have start of month , qtr and Year , then with Rank all formula will work like WTD in the Week blog , I will share here too.

Day of Month = datediff([start of month], [date],day)

In case you do not have you can get like

Start of month = minx(filter(Date, [fiscal month year] = earlier([fiscal month year])),[Date])

same is true for qtr and year

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Day of month will weekday

Your format will allow you to create the required rank.

Please try and let me know if does not work for you

[DanielWoolcott]

@amitchandak - Thank you for the additional details, they were definitly helpful.  

EDIT:  I was able to find an error in my changing of fields, which is now giving me closer results.  The issue is that It is showing this month vs last month, not a sum of the days to that point.

Calendar_Date	Start of month	Day of Month	Period Rank	This Period	Last Period	Extended Amount
4/22/2020 0:00	3/29/2020 0:00	24	16	$  1,475,827,154.67	$  2,062,666,545.98	$       74,003,020.00
4/23/2020 0:00	3/29/2020 0:00	25	16	$  1,475,827,154.67	$  2,062,666,545.98	$       70,045,252.00
4/24/2020 0:00	3/29/2020 0:00	26	16	$  1,475,827,154.67	$  2,062,666,545.98	$       74,660,962.00
4/25/2020 0:00	3/29/2020 0:00	27	16	$  1,475,827,154.67	$  2,062,666,545.98	$       14,746,879.00
4/26/2020 0:00	4/26/2020 0:00	0	17	$  1,360,203,101.06	$  1,475,827,154.67	$         6,199,568.00
4/27/2020 0:00	4/26/2020 0:00	1	17	$  1,360,203,101.06	$  1,475,827,154.67	$       71,696,123.00
4/28/2020 0:00	4/26/2020 0:00	2	17	$  1,360,203,101.06	$  1,475,827,154.67	$       71,034,793.00
4/29/2020 0:00	4/26/2020 0:00	3	17	$  1,360,203,101.06	$  1,475,827,154.67	$       59,919,502.00
4/30/2020 0:00	4/26/2020 0:00	4	17	$  1,360,203,101.06	$  1,475,827,154.67	$       80,240,118.00

[amitchandak]

@DanielWoolcott , seems like still not TD

Make sure it is

[period] = max([period])-1 && [Day of period] = max([day of period])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

with your date table

[bonafides]

Thanks @amitchandak very helpfull