Dynamic Card Title (Month / Year and Quarter)

[Gregory-N]

Hi all,

I am trying to create a Card header to a report for a client.

They require that the card / header adjusts depending if a single month, numerous months or a quarter is selected.

I managed to get the card to show if a single month and year is selected using the below measure.

Procurment Report Title = "Procurement Report for "&SELECTEDVALUE('Date'[Month], "Numerous Months in")&" "&SELECTEDVALUE('Date'[Year],"Numerous Years")

Result if ony 1 month is selected

Result if more than 1 month is selected

 I am using a date slicer with a start and end date, see below screenshot. 

So what I need is for the card to say month year (August 2022) if single month selected or month, month 2022 (July, August 2022) if more than one is selected and lastly quarter (Quarter 1 2022) if three months ie Jan through March is selected.

Not sure if we are able to add a YTD if January through to todays date is selected?

Hopefully someone out there can assist me with getting this card to work as required.

Looking forward to a response.

Kind Regards,

Gregory

[Gregory-N · Accepted solution]

Hi all,

Following a lot of Google searches I came across a Guy in a Cube video that solved my problem. 

I have shared my code as well as a link to the video.

Code: 

Selected months =

VAR SelectedMonthNumbers =

    VALUES ( 'Date'[Month Number] )

VAR MonthsAndNames =

    SUMMARIZE ( 'Date', 'Date'[Month Number], 'Date'[Month Year] )

VAR Result =

    CONCATENATEX (

        MonthsAndNames,

        VAR CurrentMonthNumber = 'Date'[Month Number]

        VAR CurrentMonthName = 'Date'[Month Year]

        VAR IsNextSelected = CurrentMonthNumber + 1 IN SelectedMonthNumbers

        VAR IsPrevSelected = CurrentMonthNumber - 1 IN SelectedMonthNumbers

        RETURN

            IF (

                NOT ( IsPrevSelected && IsNextSelected ),

                CurrentMonthName & IF ( IsNextSelected, "-", ", " )

            ),

        "",

        'Date'[Month Number]

    )

RETURN

 "Procurement Report for " &   LEFT ( Result, LEN ( Result ) - 2 )

Guy in a Cube Video: https://youtu.be/QYbl4k_CWBE

I hope this helps someone else out there experiencing a similar issue 🙂

Regards,

Gregory

[amitchandak]

@Gregory-N , Have column Month year sorted on Month year sort in your date table

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")

Then create a measure like

concatenateX(allselected('Date'), 'Date'[Month Year], " , ")

[Gregory-N]

Hi @amitchandak ,

Thanks so much for your response.

I have added the columns as well as the measure and am getting a card that has numerous dupplicate values, see below:

What else can I do to get the wording Quater 1 etc in this measure?

Looking forward to your response.

Regards,

Gregory