Forum Discussion

SM4's avatar
SM4
Regular Visitor
2 years ago
Solved

Displaying columns based on user input.

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...
  • tamerj1's avatar
    tamerj1
    2 years ago

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

  • SM4's avatar
    SM4
    Regular Visitor
    2 years ago

    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's avatar
      tamerj1
      Community Champion
      2 years ago

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

  • kai25's avatar
    kai25
    Frequent Visitor
    2 years ago

    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?

  • kai25's avatar
    kai25
    Frequent Visitor
    2 years ago

    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?