Forum Discussion

Marshmallow's avatar
Marshmallow
Icon for Helper II rankHelper II
1 year ago
Solved

Dynamic title on date hierarcy

Hi  I am trying to get dynamic title based on the date selected (which is a date hierarcy). On the Date hierarchy i selected: Year and Month.    Then I proceed with my formula is: Dynamic_...
Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi Marshmallow, try with this logic

    test1 =
VAR selectedmonthyear = values ('Calendar'[Month Year])
VAR MaxToShow = 3
VAR TotalSelected = COUNTROWS(selectedmonthyear)
VAR PeriodsToShow = TOPN(MaxToShow, selectedmonthyear, 'Calendar'[Month Year], ASC)
VAR MorePeriods = IF(TotalSelected > MaxToShow, " and " & (TotalSelected - MaxToShow) & " more", "")
VAR Result = 
"New Applications Received in: " & 
IF(TotalSelected = COUNTROWS(ALL('Calendar'[Month Year])),
"All",
CONCATENATEX(PeriodsToShow, 'Calendar'[Month Year], ", ") & MorePeriods
)
RETURN
Result

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

Marshmallow's avatar
Marshmallow
Icon for Helper II rankHelper II
1 year ago

Hi, it didn't work. However I have since come up with a solution however I need to limit the output to only shows 3 periods. are you able to assist? This is my new code:

 

test1 =
VAR selectedmonthyear =
    values ('Calendar'[Month Year])
    return
    "New Applications Received in: "
    & if(
        COUNTROWS(selectedmonthyear) = COUNTROWS(all('Calendar'[Month Year])), "All",
        CONCATENATEX(VALUES('Calendar'[Month Year]), 'Calendar'[Month Year], ","))
ajaybabuinturi's avatar
ajaybabuinturi
Icon for Super User rankSuper User
1 year ago

Hi Marshmallow, try with this logic

test1 =
VAR selectedmonthyear = values ('Calendar'[Month Year])
VAR MaxToShow = 3
VAR TotalSelected = COUNTROWS(selectedmonthyear)
VAR PeriodsToShow = TOPN(MaxToShow, selectedmonthyear, 'Calendar'[Month Year], ASC)
VAR MorePeriods = IF(TotalSelected > MaxToShow, " and " & (TotalSelected - MaxToShow) & " more", "")
VAR Result = 
"New Applications Received in: " & 
IF(TotalSelected = COUNTROWS(ALL('Calendar'[Month Year])),
"All",
CONCATENATEX(PeriodsToShow, 'Calendar'[Month Year], ", ") & MorePeriods
)
RETURN
Result

 

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.