Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have my calender table set up and I need help to get the sum of previous month and prior to previous month for the fiscal dates.
my fiscal calender FY21 starts from 22nd Jun.
->So 22nd Jun- to 27th July is consider as 1st Month of FY
-> 28th July- 22nd Aug is consider as 2nd Month of FY
--> so on..
I used this formula:
Previous month:-
Prior Previous month:-
Solved! Go to Solution.
@Anonymous , Time Intelligence will not work for you.
Create a month Rank on the month start date. Make sure you have a separate month/Date table for that
And Try measures like
Month Rank = RANKX(all('Date'),'Date'[Month Start Date ],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))
Refer my Month and Week Blog
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
@Anonymous , Time Intelligence will not work for you.
Create a month Rank on the month start date. Make sure you have a separate month/Date table for that
And Try measures like
Month Rank = RANKX(all('Date'),'Date'[Month Start Date ],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))
Refer my Month and Week Blog
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
Good morning,
I am trying your formula and am getting an erroneous result.
Using the formula you have for MTD, I would expect my result to be $1,350.64, however I am getting everything
below is the formula I am using
Can you let me know what I am doing wrong?
Hi @amitchandak ,
I am trying to follow the steps you mentioned, I created Month start of date:
@Anonymous , my assumption is that in your custom calendar you already have the start of the month. You can not use the start of month function of power bi.
If you do not have, give me a year calendar in excel I will try to create
Hi @amitchandak
Thanks for your reply.
Please find atatched.
My dataset has both the calendar and fiscal attribute,, so if user wants to see the data for calender, the switch between fiscal and calendar attribute will help them. so we have added the month and attribute to the dataset. ( you can see this in my attached).
Appreaciate our help!
Thnaks,
Tejaswi
Thanks @amitchandak & @Greg_Deckler , I will go thru the blogs and will get back for any questions.
Thanks,
Tejaswi
@Anonymous - OK, if you have a custom fiscal calendar then time "intelligence" functions are next to useless. You will need to create the filters yourself.
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
If those don't help, @ me and I'll take a deeper look at your PBIX file.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |