Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Making date x-axis dynamic in tooltip

Hello everyone, 

 

I have a dashboard with a sales table and a slicer for MTD, QTD,YTD and MAT. When the users hover over the table they should see a line chart as a tooltip that shows the growth within the period selected. I managed to make it work for MAT and YTD. The x-axis of the tooltip visual for these two selections is in months. But for MTD and QTD I would need the days or weeks on the x-axis. Does someone know how to make the x-axis dynamic so that when selecting MTD it would show the days and when selecting QTD it would shouw the weeks?

 

this is for example the tooltip when selecting TAM and August as month; we see the growth from the last 12 months. But when I select MAT nothing appears. I already have a date column on date level but it does not work. 

 

 

Thank you for your help!

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Anonymous 

    I am glad to help you.

     

    Perhaps you can try to create dynamic x-axis field parameters to fulfill your needs.


    First you need to create a DateTable, which should contain columns for Year, Quarter, Month, Week and Day:

    DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN('SalesTable'[Date]), MAX('SalesTable'[Date])),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", YEAR([Date]) * 100 + MONTH([Date]),
    "Week", WEEKNUM([Date]),
    "Day", DAY([Date]),
    "Day of Week", FORMAT([Date], "dddd")
)


    Then create a relationship between the DateTable and the original table:

     


    Create a field parameter, select the Day, Week, Month field of the DateTable:

     


    Then add a new Page2, add a Line chart visual, drag the corresponding fields to the visual, and set it as Tooltips:

     

     


    Then apply the customized Tooltips to the visual in Page1:

     

    Result:

     

     

    I have attached the pbix file for this small example below, hope it helps.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Anonymous 

    I am glad to help you.

     

    Perhaps you can try to create dynamic x-axis field parameters to fulfill your needs.


    First you need to create a DateTable, which should contain columns for Year, Quarter, Month, Week and Day:

    DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN('SalesTable'[Date]), MAX('SalesTable'[Date])),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", YEAR([Date]) * 100 + MONTH([Date]),
    "Week", WEEKNUM([Date]),
    "Day", DAY([Date]),
    "Day of Week", FORMAT([Date], "dddd")
)


    Then create a relationship between the DateTable and the original table:

     


    Create a field parameter, select the Day, Week, Month field of the DateTable:

     


    Then add a new Page2, add a Line chart visual, drag the corresponding fields to the visual, and set it as Tooltips:

     

     


    Then apply the customized Tooltips to the visual in Page1:

     

    Result:

     

     

    I have attached the pbix file for this small example below, hope it helps.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.