cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Accepted Solutions
Super User IV
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))

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.

 



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!

View solution in original post

6 REPLIES 6
Super User IV
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))

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.

 



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!

View solution in original post

@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_DateDayOfWeekDayOfYearDayofQuarterWeekOfYearPeriodOfYearQtrFiscalYearFiscalWeekFiscalPeriodFiscalQuarter
6/15/2020 0:00217079256220202020-252020-062020-02
6/14/2020 0:00116978256220202020-252020-062020-02
6/13/2020 0:00716877246220202020-242020-062020-02
6/12/2020 0:00616776246220202020-242020-062020-02
6/11/2020 0:00516675246220202020-242020-062020-02

 

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

Shiphead Invoice DateExtended 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)

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

@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_DateStart of monthDay of MonthPeriod Rank This Period  Last Period  Extended Amount 
4/22/2020 0:003/29/2020 0:002416 $  1,475,827,154.67 $  2,062,666,545.98 $       74,003,020.00
4/23/2020 0:003/29/2020 0:002516 $  1,475,827,154.67 $  2,062,666,545.98 $       70,045,252.00
4/24/2020 0:003/29/2020 0:002616 $  1,475,827,154.67 $  2,062,666,545.98 $       74,660,962.00
4/25/2020 0:003/29/2020 0:002716 $  1,475,827,154.67 $  2,062,666,545.98 $       14,746,879.00
4/26/2020 0:004/26/2020 0:00017 $  1,360,203,101.06 $  1,475,827,154.67 $         6,199,568.00
4/27/2020 0:004/26/2020 0:00117 $  1,360,203,101.06 $  1,475,827,154.67 $       71,696,123.00
4/28/2020 0:004/26/2020 0:00217 $  1,360,203,101.06 $  1,475,827,154.67 $       71,034,793.00
4/29/2020 0:004/26/2020 0:00317 $  1,360,203,101.06 $  1,475,827,154.67 $       59,919,502.00
4/30/2020 0:004/26/2020 0:00417 $  1,360,203,101.06 $  1,475,827,154.67 $       80,240,118.00

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

Thanks @amitchandak 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors