Help with changing the text within a text box depending on the date/quarter/year filters

[brookesmoore]

Hello All,

I have a series of cards aligned in a row displaying the sums of various revenue types. Each revenue type has its own column in the table and is linked to a date spanning from 1/1/2022 to today. 

Below each card is a text box showing a percentage that denotes the portion of the revenue that goes to the client (text is "98%").

Below each text box I have another series of cards showing a measure of Revenue Type * Percentage giving the total.

On 2/1/2023, a couple of the percentages were changed (98% to 94%), thus requiring a new calculation to get the total due to the client for any transactions from 2/1/2023 and on.

I have a slicer with a Date Slider, a slicer with 2022 and 2023 as tiles, and a slicer with 4 quarters as tiles. 

Im looking to see if there is a way to have the percentages in the text boxes change depending on what date filters are being used on the page. For example, if the date range is before 2/1/23, I want the text to display "98%". If after 2/1/23, it should say "94%". If the date range includes dates from both before and after 2/1/23, I want the text box to display "98%/94%" (or just blank, i havent decided what will look better for this situation).

Is any of this possible? Any leads are greatly appreciated. Happy to provide additional info if needed. Thanks!

[v-yiruan-msft · Accepted solution]

Hi @brookesmoore ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create a measure as below

Measure = 
VAR _mindate =
    MIN ( 'Date'[Date] )
VAR _maxdate =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        _maxdate < DATE ( 2023, 2, 1 ),
        0.98,
        IF ( _mindate >= DATE ( 2023, 2, 1 ), 0.94, BLANK () )
    )

2. Create a card visual to replace the textbox

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @brookesmoore ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create a measure as below

Measure = 
VAR _mindate =
    MIN ( 'Date'[Date] )
VAR _maxdate =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        _maxdate < DATE ( 2023, 2, 1 ),
        0.98,
        IF ( _mindate >= DATE ( 2023, 2, 1 ), 0.94, BLANK () )
    )

2. Create a card visual to replace the textbox

Best Regards

[brookesmoore]

Thank you for your reply. This is working so far. 

I would like to see if there is a way to display the text "98%/94%" should the date range fall into both ranges (before or on 1/31/2023 and after or on 2/1/2023). Is this possible?

[v-yiruan-msft · Accepted solution]

Hi @brookesmoore ,

You can make a little adjustment on the measure as below, please find the details in the attachment.

Measure =

VAR _mindate =

    MIN ( 'Date'[Date] )

VAR _maxdate =

    MAX ( 'Date'[Date] )

RETURN

    IF (

        _maxdate < DATE ( 2023, 2, 1 ),

        0.98,

        IF ( _mindate >= DATE ( 2023, 2, 1 ), 0.94, "98%/94%" )

    )

Best Regards

[brookesmoore]

Works perfectly. Thank you!