cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Gregory-N
Regular Visitor

Dynamic Card Title (Month / Year and Quarter)

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
GregoryN_0-1664797384370.png

Result if more than 1 month is selected

GregoryN_2-1664797594102.png

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

 

GregoryN_1-1664797447254.png

 

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

 

1 ACCEPTED SOLUTION
Gregory-N
Regular Visitor

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
 

View solution in original post

3 REPLIES 3
Gregory-N
Regular Visitor

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
Super User
Super User

@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], " , ")

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:

 

GregoryN_0-1664804755601.png

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

 

Looking forward to your response.

 

Regards,

 

Gregory

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors