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
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,

     

    • NeilJustice's avatar
      NeilJustice
      Frequent Visitor
      2 years ago

      Hi DataNinja777 

      This is now showing Dec 24 as you have stated above, so it works in that respect, but i dont want it to do that, i want it to show the previous full calendar month to what the current month is. So when nothing is selected, as of today i would expect to see the month for Apr 24 and this point in time next month (June) i would expect to see May 24 if nothing is selected. So the first part of the expression works fine, its just getting the second part to default to the previous month . cheers

      Addition:- 

      I have amended the expression so that it now shows the previous month if none of the values in the slicer are selected. But it doesnt filter the values in the visual to show that month, It shows the totals for all the dates, i am guessing that this is because the visual is theoretically not being filtered, only the visual title. It only filters when i select a date in my slicer, how would we amend this to actually show the correct values when filtered. 

      The amended expression is 

      Month title = 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] ),FORMAT(EOMONTH(TODAY(), -1), "mmm - yy"))),
          CONCATENATEX ( VALUES ( 'Date Table Invoice creation date'[Month Year]), 'Date Table Invoice creation date'[Month Year], ", " ))