Forum Discussion
Date Issues in Visuals
Hi,
I have a problem with my Matrix visuals. From the 1st January they are showing January before April which is incorrect, my Financial Year starts in Apr so January should come after December:
If I drill down then the order is correct but I do not want to see Percentages for 2025 like below:
The problem I have is the Resolution Date field is not a continuous set of dates, not every Item has a Resolution Date, therefore the Resolution Date does not have a relationship with my Calendar Table, instead I use the 'Created On' date. The Created On date is different to the Resolution Date and so I cannot use that date.
If I try to create a relationship between the Date2[Date] and Resolution Date a lot of my Visuals lose the month in the x axis and are replaced by individual days like below:
I think we will need to get an external consultant to fix this as the data model is very complicated and its not one I can share on this forum either.
Does anyone have any suggestions? I'm happy to provide further detail and screenshots if necessary.
Thanks,
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
10 Replies
Thanks again for all your help!
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.
Thanks 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.ArchStanton - 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.
I absolutely need the Resolution Date for visuals that show Closed Items, Open items dont have a Resolution Date.
The visuals breakdown as per screenshot above - months are replaced by individual days.I think I've figured out a solution, I have made a copy of my Date Calendar table "ResolutionDateCalendar" and created an active relationship between the Resolution Date and this new calendar, it seems to be working fine!
I think you prompted me to think about this problem in a different way which helped!😊
ArchStanton - Great that you have resolved it. But my original suggestion of using an inactive relationship and USERELATIONSHIP in a measure would have also resolved this without the need for an additional calendar.
The reason your visuals "breakdown" is not due to the inactive relationship, it is due to the fact that you have used the "Resolution date" column in the visual not the month and year columns from your calendar.
Ok undertood. I'm not quite proficient enough with DAX to test that - perhaps when I get a bit of time I will explore your suggestion and maybe contact you on here if thats ok?
