Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
1 year ago
Solved

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: ...
  • mark_endicott's avatar
    1 year ago

    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.