cancel
Showing results for
Did you mean:
Frequent Visitor

## Day of Fiscal Calendar

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!

1 ACCEPTED SOLUTION
Super User IV

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

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

6 REPLIES 6
Super User IV

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

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

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

Super User IV

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

@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
Super User IV

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

Thanks @amitchandak

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!