Dynamically columns select in table visual

Revised Answer:

To dynamically select columns in a table visual and automatically handle the addition and removal of columns based on your requirements, you can achieve this in Power BI by using DAX expressions and data modeling. Here's a step-by-step guide on how to do it:

1. Data Preparation:

   • Ensure your data source includes a Date column and a Value column.
   • Create a Date table with a continuous sequence of dates covering the entire range of months you expect in your dataset.

2. Data Modeling:

   • Create a relationship between your main data table and the Date table using the Date column.
   • Create a calculated column that generates a flag for the 12 months you want to display. You can use the following DAX expression as an example:

Use Dax:

Display Month Flag =
IF(
AND(
[Date] >= TODAY(),
[Date] < TODAY() + 365,
MONTH([Date]) <= MONTH(TODAY()) + 12
),
1,
0
)

1. This calculated column will evaluate to 1 for the 12 months starting from the current month and 0 for other months.

2. Table Visual:

   • Create a table visual.
   • In the Values field well of the table visual, place your Value column.
   • In the Rows or Columns field well of the table visual, place your Date column from the Date table.

3. Visual-Level Filter:

   • Apply a visual-level filter to your table visual by setting the "Display Month Flag" to 1.

Now, your table visual should dynamically display the data for the current month plus the next 11 months, automatically removing the oldest month as time progresses.

Additionally, to automatically sum the data for the displayed 12 months, you can use another DAX measure:

Use Dax:

Total 12 Months Value = SUMX(FILTER('YourData', [Display Month Flag] = 1), 'YourData'[Value])

You can place this measure in your table visual, and it will show the sum of the displayed 12 months' data.

Remember to replace 'YourData' with the name of your actual data table.

By following these steps and creating calculated columns and measures based on the "Display Month Flag," you can achieve the dynamic column selection and summation in your table visual as per your requirements.

and 

If you want to dynamically change the columns in a table visual based on your data and have products as columns and months as rows, you can indeed use the Power Query's Transpose feature. Here's how you can do it:

1. Data Preparation:

   • Ensure you have your data with columns like Sep-23, Oct-23, Nov-23, etc., and products in rows.

2. Power Query (Data Transformation):

   • Load your data into Power Query.
   • Follow these steps in Power Query to transpose your data:
     1. Select the first row (which contains the month names) in your data table.
     2. Right-click on the selected row and choose "Transpose."
     3. This will pivot your data so that the months become rows, and the products become columns.

3. Data Modeling in Power BI:

   • After transposing your data in Power Query, load it back into Power BI.
   • You will now have a table with columns for each product and rows for each month.

4. Measures:

   • Create measures for each product and month as needed. These measures will aggregate the data based on your business requirements. For example, if you want to sum the values for each product and month, you can create measures like this:

Use Dax:

Total_Sep_23 = SUM('YourTable'[Sep-23])
Total_Oct_23 = SUM('YourTable'[Oct-23])
-- Repeat for all months

1. Replace 'YourTable' with the actual name of your table.

2. Table Visual:

   • Create a table visual in Power BI.
   • In the Values field well of the table visual, place your measures (e.g., Total_Sep_23, Total_Oct_23, etc.).
   • In the Rows field well of the table visual, place the Date column.

With this approach, your table visual will dynamically show columns for each product and rows for each month, and the data will be aggregated according to the measures you created. As you add new data for future months, you won't need to manually adjust the columns in the visual; they will adapt automatically based on your transposed data structure.

If I answered your question, please mark my post as solution, Appreciate your Kudos.