Displaying columns based on user input.

[SM4]

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.

[tamerj1 · Accepted solution]

Hi @SM4 
Please refer to attached sample file the proposed solution

[tamerj1 · Accepted solution]

Hi @SM4 
Please refer to attached sample file the proposed solution

[kai25]

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?

[kai25]

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?

[SM4]

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

[tamerj1 · Accepted solution]

@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] )
        )
    )

[v-jtian-msft]

Hi @SM4 ,

Thanks for the reply from @tamerj1@Anonymous , 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.

[tamerj1]

Hi @SM4 
Please provide sample input data

[SM4]

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

[Anonymous]

+1