Filter on Month by colum

[Anonymous]

Hi all,

For a project i need to filter products by season. 

the columns are the months and if there's a 1 , it s an active product en a 0 for inactive products that month.

I've no clue how to filter this on active months. could yall help me out?

this is a snippet of the data.

[Anonymous · Accepted solution]

Thanks for your input @PC2790 @rohit_singh,

To be honest. I fixed this myself in a unorthodox way I assume.

By making an activation date and deactivation date and filtering on the month of the date i achieved enough.

_IsActive = IF(MONTH(NOW()) >= Month(Blad1[ActivationDate])  && MONTH(NOW()) <= MONTH(Blad1[DeActivationDate]),1,0)

This isn't made to last but gets the job done. Besides that, it's only useful as long as i've continuing periods of activation.

Because that's the case, it gets the job done.

Again thanks for the input.

[PC2790]

You need to unpivot the columns.

For that go to Power Query by going to Transform Data.

Select the columns-->Right click --> Unpivot only selected columns. Like this:

Rename as per your convenience and then close and apply and you are good to go for the filtering

Does this answer your question?

[rohit_singh]

Hi @Anonymous ,

In order to filter active orders by month, you will need to unpivot only the month columns in power query to get a structure like this :

You will then be able to use month and value as slicers

Sample code is given below :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQDJR0lAyg2JIBh6iIio5RidaKVjED6jSgwwBhkgDG5BsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ArticleCode = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t, #"11" = _t, #"12" = _t, Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ArticleCode", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}, {"11", Int64.Type}, {"12", Int64.Type}, {"Category", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ArticleCode", "Category"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Month"}})
in
#"Renamed Columns"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊