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: ...
Need Help
Show and Tell
  • mark_endicott's avatar
    mark_endicott
    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. 

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

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!😊

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

    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. 

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

      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?

      • mark_endicott's avatar
        mark_endicott
        Icon for Super User rankSuper User
        1 year ago

        ArchStanton - That's fine, USERELATIONSHIP is very simple though, essentially all you need to do to invoke it and make the relationship active is write a measure like so:

         

        CALCULATE (
            [Your Measure Here],
            USERELATIONSHIP ( Fact Table[Resolution Date], 'Date'[Date] )
        )

         

        And then use this in your visual instead. 

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

      Ps also, I have dozens of measures that will need to be updated with USERELATIONSHIP which will cost me a lot of time - i don't suppose there is a quick way of updating these If I decide to go down your route in future is there?