topic Re: Displaying columns based on user input. in DAX Commands and Tips

Displaying columns based on user input.

SM4 — Wed, 17 Apr 2024 03:15:23 GMT

I am new to power bi so please forgive me if its a simple silly solution.

I am working with data which has value for month-year for 2 years i.e. 24 columns, i want to creat a table/matrix wherein based on the users input of month and year all the columns till then would be displayed, for example if user input is Aug 23 all months from Jan 23 till Aug 23 should be displayed.

Re: Displaying columns based on user input.

Anonymous — Wed, 17 Apr 2024 05:05:24 GMT

Re: Displaying columns based on user input.

tamerj1 — Wed, 17 Apr 2024 05:33:35 GMT

Hi SM4
Please provide sample input data

Re: Displaying columns based on user input.

SM4 — Wed, 17 Apr 2024 07:51:00 GMT

https://docs.google.com/spreadsheets/d/1nHl88a0jURhK9H_iXLczpErvbcp7SKne7ow3eBaAJY4/edit?usp=drivesdk

Re: Displaying columns based on user input.

Anonymous — Wed, 17 Apr 2024 09:58:50 GMT

Hi SM4 ,

Thanks for the reply from tamerj1Anonymous , please allow me to provide another insight:

Thanks for your concern about this case.
I noticed you sent the data link but unfortunately I don't have permission to access it.
When I try to access the data, the page shows the following:

I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
The first thing I did was to create a date table with two years of time using the dax function.

Here is the dax function:

Table 2 = CALENDAR("2023-1-1","2024-12-31")

I then duplicated this table and now have two identical data tables

Table = CALENDAR("2023-1-1","2024-12-31")

I am creating a slicer with the table's column field with month as the filter criteria

You can add filters by year and month to the slicer clock.

I then used table2 to display the final results of the filter, and created a measure for table2 called "M_".
here is the dax code for ”M_

M_ = VAR _Slicer = MAX ( 'Table'[Date] ) VAR _Slicermonth = MONTH ( _Slicer ) RETURN IF ( MAX ( 'Table 2'[Date] ) <= _Slicer && YEAR ( MAX ( 'Table 2'[Date] ) ) = YEAR ( _Slicer ), MONTH ( MAX ( 'Table 2'[Date] ) ), BLANK () )

The max function will filter out the maximum value of the current filter conditions

Now you can click on a month of a year in the filter to filter out data from January to the selected month of the year.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Displaying columns based on user input.

tamerj1 — Wed, 17 Apr 2024 10:55:20 GMT

Hi SM4
Please refer to attached sample file the proposed solution

Re: Displaying columns based on user input.

SM4 — Tue, 30 Apr 2024 10:15:43 GMT

thank you so much, it helped a lot.

Is there a way to add a year filter as well? eg. Apr 24 would show only jan 24 to apr 24 not the other year

Re: Displaying columns based on user input.

tamerj1 — Wed, 01 May 2024 09:58:11 GMT

SM4

Month YTD = VAR Months = ALLNOBLANKROW ( 'Table'[Month Name], 'Table'[Month] ) RETURN GENERATE ( Months, FILTER ( SELECTCOLUMNS ( Months, "@Month", 'Table'[Month] ), [@Month] <= 'Table'[Month] && YEAR ( [@Month] ) = YEAR ( 'Table'[Month] ) ) )

Re: Displaying columns based on user input.

kai25 — Tue, 28 May 2024 13:41:42 GMT

its a great solution 😃

just a question in the data set i see there is a column called target, in your solution i see its lost, if i suppose i want to keep the target column in my visual how would i do that?

Re: Displaying columns based on user input.

kai25 — Wed, 29 May 2024 18:45:45 GMT

Also if i want to create a column in the visual to show the average of a few months sale, where the start month and end month is a user input,

how would I do that?