Forum Discussion

NeilJustice's avatar
NeilJustice
Frequent Visitor
2 years ago

Dynamic Title based on selected month values on slicer

I have a slicer that has Fiscal years, quarters and months for selection, i want to create a dynamic title for my other visual that when i select a month or multiple months, quarter or FY the title d...
Need Help
Show and Tell
Tips and Tricks
NeilJustice's avatar
NeilJustice
Frequent Visitor
2 years ago

Hi DataNinja777     Nearly. but this is what i get when nothing is selected 

and this is what i get when i select month or months 

I want it to show the last full calendar month from the point in time i am looking at report. when nothing is selected, is this possible? Thanks in advance 

ps is it because i am trying to add the dynamic subtitle visual, should it be on a seperate card as in yours?

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi NeilJustice 

     

    You can create your dynamic title in your pie chart in the same manner, instead of creating a separate card visual.  

    I tweaked the formula a little bit, and you can flexibly change the output by tweaking if your required output is different.  I attach an example pbix file.  

    Best regards,

     

    • NeilJustice's avatar
      NeilJustice
      Frequent Visitor
      2 years ago

      Hi, 

      I have replicated the above in my report, the month or months filter works fine but when i have nothing selected it now returns Sep24. and the pie chart, shows all values for the date period in my calendar.  The calendar is created from taking the min & max dates of "invoice creation date" in my main data table as in expression below 

      Date Table Invoice creation date = CALENDAR(
      DATE(YEAR(MIN('Table' [Invoice Creation Date])),1,1),
      DATE(YEAR(MAX('Table' [Invoice Creation Date])),12,31)), and my expression i used to replicate yours is 
       
      Monthbymonth view =
      IF (
          DISTINCTCOUNT ( 'Date Table Invoice creation date'[FY Month No] ) = 12,
          calculate(if(hasonevalue('Date Table Invoice creation date'[Month Year] ),values('Date Table Invoice creation date'[Month Year] ),blank()),filter('Date Table Invoice creation date','Date Table Invoice creation date'[End of Month]=max('Date Table Invoice creation date'[End of Month]))),
          CONCATENATEX ( VALUES ( 'Date Table Invoice creation date'[Month Year]), 'Date Table Invoice creation date'[Month Year], ", " ), so not sure why its returning Sep 24 when nothing is selected in my slicer.
      what i want it to show is the last relevant full calendar month and the correct values for that month in my pie chart.
      e.g No dates selected in slicer, returns Apr 24 and its values, if it were todays date. if this is possible
      • DataNinja777's avatar
        DataNinja777
        Icon for Super User rankSuper User
        2 years ago

        Hi NeilJustice 

        The fact that your filter of the max of End of Month is showing as Sep-24 instead of Dec-24 is due to the fact that your month order is in alphabetical order instead of numerical order.  What is the data type of your  'Date Table Invoice creation date'[End of Month]?  It should be in a date format instead of text format.  

        Best regards,