Forum Discussion

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

Adding column to display the Month Parameters

I have added a time period selectionfor the previous 1, 3, 6, 13 to display different views of the data.  I feel like the calendar hierarchy can be confusing if I don't show a visula of the periods t...
DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
1 year ago

Hi brockry1 ,

 

To display the selected time period parameters (Previous 1, 3, 6, 13) along with their corresponding YearMonthValue in the designated space, you can create a DAX measure that dynamically updates based on the selected time period. The measure retrieves the selected value from the slicer and filters the CalendarTable to return the corresponding YearMonthValue for the selected number of periods. It uses the TOPN() function to get the top periods based on RankByYearMonth and concatenates them into a readable format.

Selected Periods = 
VAR SelectedPeriod = SELECTEDVALUE('Time Period Selection'[Previous N Months])
VAR Periods = 
    CALCULATE(
        CONCATENATEX(
            TOPN(SelectedPeriod, 'CalendarTable', 'CalendarTable'[RankByYearMonth], DESC),
            'CalendarTable'[YearMonthValue], ", "
        ),
        ALLSELECTED('CalendarTable')
    )
RETURN "Selected Periods: " & Periods

This measure ensures that the displayed periods dynamically update based on the selection made in the slicer. Once created, you can place it in a Card visual or a Text Box in your Power BI report to make it easily visible. This approach will provide a clear reference for users, making it evident which periods are included in the selected time frame.

 

Best regards,

  • brockry1's avatar
    brockry1
    Icon for Helper II rankHelper II
    1 year ago

    I tried using the above:

     

    Selected Periods =
    VAR SelectedPeriod = SELECTEDVALUE('N Months'[Previous N Months])
    VAR Periods =
    CALCULATE(
        CONCATENATEX(
            TOPN(SelectedPeriod, 'Account Data', 'Account Data'[RanksByYearMonth], DESC),
            'Account Data'[YearMonthValue],","
        ),
        ALLSELECTED('Account Data'[YearMonthValue])
    )
    RETURN "Selected Periods: " & Periods
     
    and it returns this error.
     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi brockry1 

       

      "Previous 1" is of Text data type while TopN function requires an integer number as the first parameter, that's why you got this error. To resolve it, you can modify the measure to extract the number of periods and convert it into a number:

       

      Selected Periods =
      VAR SelectedPeriod = VALUE( TRIM( RIGHT( SELECTEDVALUE('N Months'[Previous N Months]), 2)))
      VAR Periods =
      CALCULATE(
          CONCATENATEX(
              TOPN(SelectedPeriod, 'Account Data', 'Account Data'[RanksByYearMonth], DESC),
              'Account Data'[YearMonthValue],","
          ),
          ALLSELECTED('Account Data'[YearMonthValue])
      )
      RETURN "Selected Periods: " & Periods
       
       
      Best regards,
      Jing
    • v-saisrao-msft's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi brockry1,

       

      We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

       

      Thank you.

      • brockry1's avatar
        brockry1
        Icon for Helper II rankHelper II
        1 year ago

        I no longer need this dashboard or calculation.