Forum Discussion

rivera11115's avatar
rivera11115
Regular Visitor
3 years ago
Solved

Dynamic card based on year slicer

Hi,   I want the dynamic card to read as follows: Sales year between 2013 and 2016 -- only if a range of consecutive years are selected ex 2013,2014,2015 & 2016. Sales year for 2012, 2014, 2015 a...
Need Help
  • rivera11115's avatar
    rivera11115
    3 years ago

    Hi..thank you so much for helping out.

    Your code works for the first two scenarios (see original question) but when a single year is chosen the year repeats in the header twice.

    I was able to tweek your code a little and it works great thank you so much!

    DateRange =
    VAR MaxYear =
        MAX ( 'Calendar'[Year])
    VAR MinYear =
        MIN ( 'Calendar'[Year] )
    VAR Difference = ( MaxYear - MinYear ) + 1
    VAR TotalSelectedValues =
        DISTINCTCOUNT ( 'Calendar'[Year] )
    RETURN
    IF(TotalSelectedValues= 1, "Sales Year " & CONCATENATEX (VALUES ( 'Calendar'[Year] ), 'Calendar'[Year], " " ),
        IF (
            Difference > TotalSelectedValues,
            "Sales Year for " & CONCATENATEX ( VALUES ('Calendar'[Year] ), 'Calendar'[Year], ", " ),
            IF (
                Difference = TotalSelectedValues,
                "Sales Year Between " & MinYear & " and " & MaxYear,
                IF ( HASONEVALUE ('Calendar'[Year]), SELECTEDVALUE ( 'Calendar'[Year] ) )
            )
        )
    )
rajulshah's avatar
rajulshah
Resident Rockstar
3 years ago

rivera11115 ,

 

Please use the following dax measure:

SelectedYearValues =
VAR MaxYear =
    MAX ( Dates[Year] )
VAR MinYear =
    MIN ( Dates[Year] )
VAR Difference = ( MaxYear - MinYear ) + 1
VAR TotalSelectedValues =
    DISTINCTCOUNT ( Dates[Year] )
RETURN
    IF (
        Difference > TotalSelectedValues,
        CONCATENATEX ( VALUES ( Dates[Year] ), Dates[Year], ", " ),
        IF (
            Difference = TotalSelectedValues,
            "Between " & MinYear & " and " & MaxYear,
            IF ( HASONEVALUE ( Dates[Year] ), SELECTEDVALUE ( Dates[Year] ) )
        )
    )

 

Please let me know if this didn't help.

  • rivera11115's avatar
    rivera11115
    Regular Visitor
    3 years ago

    Hi..thank you so much for helping out.

    Your code works for the first two scenarios (see original question) but when a single year is chosen the year repeats in the header twice.

    I was able to tweek your code a little and it works great thank you so much!

    DateRange =
    VAR MaxYear =
        MAX ( 'Calendar'[Year])
    VAR MinYear =
        MIN ( 'Calendar'[Year] )
    VAR Difference = ( MaxYear - MinYear ) + 1
    VAR TotalSelectedValues =
        DISTINCTCOUNT ( 'Calendar'[Year] )
    RETURN
    IF(TotalSelectedValues= 1, "Sales Year " & CONCATENATEX (VALUES ( 'Calendar'[Year] ), 'Calendar'[Year], " " ),
        IF (
            Difference > TotalSelectedValues,
            "Sales Year for " & CONCATENATEX ( VALUES ('Calendar'[Year] ), 'Calendar'[Year], ", " ),
            IF (
                Difference = TotalSelectedValues,
                "Sales Year Between " & MinYear & " and " & MaxYear,
                IF ( HASONEVALUE ('Calendar'[Year]), SELECTEDVALUE ( 'Calendar'[Year] ) )
            )
        )
    )