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 that are being selected.  In the account data I have a "YearMonthValue" and RankbyYearMonth starting with 13 down to 1.  So rank 13 would represent the last completed period 202502.  So realisticly I would want to show for each time period selection.  For example Previous = 202502, 202501, 202413 which would be ranks for 13, 12, 11 then same for previous 6 and 13.  I have visuals below for a reference.

 

Goal would be to say where the arrow is to show what year and period is for each previous time periods and update each period.  I think it should be an added column measure but not sure. 

8 Replies

  • 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,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi brockry1,
    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank you.