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.
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!
Solved! Go to 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
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@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
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@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)
@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
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
@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 |
@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
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |