Forum Discussion
Power ParticipantDate Issues in Visuals
ArchStanton - Have you tried using an inactive relationship between "Resolution Date" and then creating a special measure for this visual that leverages USERELATIONSHIP( ) to activate the relationship?
Here's some guidance on the use of this DAX function: https://dax.guide/userelationship/
If this doesnt work, it could also be more simple, and to do with the fact you're using a fiscal / financial month ordering with a calendar year. An additional column in your calendar that presents the fiscal / financial year could resolve this.
Here is an example of some M Code I use to create a fiscal / financial year column
InsertFYStart = Table.AddColumn(InsertWeekOfYear, "FYStart", each if [Month Number] <= 3 then Date.FromText(Number.ToText(Date.Year([Date])-1) & "-04-01") else Date.FromText(Number.ToText(Date.Year([Date])) & "-04-01"),type date), InsertFinancialYear = Table.AddColumn(InsertFYStart, "FinancialYear", each Number.ToText(Date.Year([FYStart]))&"/"& Text.End(Text.From(Date.Year([FYStart]) +1), 2)),If this helps guide you towards a fix, please accept as the solution, which will help others with the same challenge.
ArchStanton - For this I would enable the preview features show below:
And then edit your measures as one using VS Code - there's some great guidance on getting started with that in this video: https://www.youtube.com/watch?v=TPASF4HlEOA
Power ParticipantThanks for the reply.
Just to reiterate, my Fiscal Year is based on the Created On date, the Created On date has an active relationship with my Date[Date2] Calendar column which drives 80% of my reporting.
Unfortunately, I have a Resolution Date in the same Fact Table that is not linked to my Calendar, when I do try to create an inactive relationship a lot of my visuals breakdown.
I do have a second fact table called Closed Cases that I could in theory use but that would require replicating the 3 monthly Bins (0-3mths / 3-6mths etc) in that table and that will slow down the performance of my Data Model even more - it can take several seconds to run a measure at present.
Super UserArchStanton - Ok so there are a couple of things that are still not clear.
1) If you do not need "Resolution Date" for this visual, why is it causing you an issue?
2) How does an inactive relationship cause your visuals to breakdown? - If you only need these visuals to work based on "Created On" then this question is not important.
3) Do you have a column in your Date table that sorts your text months (Apr, May etc) by their order in the fiscal calendar? e.g. Apr = 1, May = 2, March = 12 ? - This will resolve the ordering issue you observe in your first screenshot.