topic Re: Adding column to display the Month Parameters in DAX Commands and Tips

Adding column to display the Month Parameters

brockry1 — Fri, 07 Mar 2025 23:59:39 GMT

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.

Re: Adding column to display the Month Parameters

DataNinja777 — Sat, 08 Mar 2025 11:42:45 GMT

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,

Re: Adding column to display the Month Parameters

brockry1 — Sat, 08 Mar 2025 20:25:40 GMT

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.

Re: Adding column to display the Month Parameters

Anonymous — Mon, 10 Mar 2025 06:34:44 GMT

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

Re: Adding column to display the Month Parameters

v-saisrao-msft — Wed, 23 Apr 2025 07:23:31 GMT

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.

Re: Adding column to display the Month Parameters

v-saisrao-msft — Mon, 28 Apr 2025 11:49:13 GMT

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.

Re: Adding column to display the Month Parameters

v-saisrao-msft — Fri, 02 May 2025 10:55:01 GMT

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.

Re: Adding column to display the Month Parameters

brockry1 — Fri, 02 May 2025 12:05:40 GMT

I no longer need this dashboard or calculation.

Re: Adding column to display the Month Parameters

v-saisrao-msft — Tue, 06 May 2025 06:04:34 GMT

Hi brockry1 ,
Thank you for confirming the thread status. If you encounter any further issues with Power BI and Fabric, please reach out to the Microsoft Fabric Community for further assistance.
Home - Microsoft Fabric Community

Thank you.