Forum Discussion
Dynamic Fiscal Year
Hello, this is my first post and I have been having an issue over some time. My visuals contain a filter for various "Schemes", each of these schemes have different fiscal years and hence not possible to use most of the static DAX formulae I have encountered online.
e.g (https://powerobjects.com/2018/12/19/sorting-data-fiscal-year-power-bi/)
My Table "Schemes" has the StartDate and EndDate.
Another Table "Payments" has the "ActualDate" for when these payments were made.
I would like to create a Month on Month analysis where I match the Payment Amount next to the month and Year.
However, the data is not accurate due to the year.
Any help on how I can overcome such.
On the image attached, this particular scheme begins in October and hence this is still 2019
3 Replies
- Greg_Deckler
Community Champion
geekmachine811 Not really enough to go on, would need to see sample data and expected output. However, I literally just posted a dynamic DAX Custom 445 Calendar to the Quick Measures Gallery and this could be included in a measure:
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Custom-445-Calendar/td-p/1388582
Also, you *should* be able to do anything you can with DAX time "intelligence" functions without those functions. 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
Ultimately though, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Thanks alot Greg_Deckler for the insights. I have gone through them and learned quite a few new stuff.
Prior to my message, in regard to schemes, check the image below.
I have over 100 Schemes with different start and end dates. As earlier mentioned, I need to create a dynamic fiscal period without having to statically infuse it to a measure and always change the beginning month as per the scheme.
- Greg_Deckler
geekmachine811 OK, you can create a dynamic calendar in a measure like this:
Measure = VAR __Calendar = CALENDAR([StartDate],[EndDate]) <something> RETURN <something>The problem is, I do not understand what needs to go in the <something>
