Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Display data based on a condition on year/month column

Hey PBi Community,   This is in corelation with my previous post requesting help. Here is a much clearer picture. I have data spanning multiple years.  The column I have: "week.year" (e.g. contain...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    you didn't answer if only -2 year should consideres... however this solution adds a new column in power query and sets true on all current months in every year present.

    Add a new column and put this syntax

    Date.Month(DateTime.FixedLocalNow()) = Number.From(Text.Split(_[week.year],"."){0})

    Here the complete solution

    let
	Source = #table
	(
		{"week.year"},
		{
			{"01.2018"},	{"02.2018"},	{"03.2018"},	{"01.2019"},	{"02.2019"},	{"03.2019"},	{"01.2020"}
		}
	),
	AddColumn = Table.AddColumn
	(
		Source,
		"isCurrentMonth",
		each Date.Month(DateTime.FixedLocalNow()) = Number.From(Text.Split(_[week.year],"."){0})
	)
in
	AddColumn

    Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

what you mean by allow? you mean a custom column like a flag, the is set to true if the month is the same as the current month?

Other question... what should happen with the jannuary of 2017?

 

Jimmy

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Answering other question first: if the current month is January then show the January data in the sense that the rows corresponding to those values where week.year is something like 01.2020 should be displayed all through the report in different visualizations. (The visualizations are already created and this step came in the requirements later on. )

 

Answering the first question: a flag like something could potentially work but not in the manner that it is set to true if the month is current month, but in the sense that if the current month is January then the rows corresponding where week.year has the values like __.2018 and __.2019 and 01.2020 is set to true otherwise if the current month is not january then set to true in rows where the week.year column has values like __.2019 and __.2020

 

here __.2019 means iteration of weeks in the year 2019 for eg: 01.2019, 02.2019,03.2019

The coulmn week.year contains values like: 

01.2018

02.2018

03.2018

.

.

01.2019

02.2019

03.2019

.

.

01.2020

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    you didn't answer if only -2 year should consideres... however this solution adds a new column in power query and sets true on all current months in every year present.

    Add a new column and put this syntax

    Date.Month(DateTime.FixedLocalNow()) = Number.From(Text.Split(_[week.year],"."){0})

    Here the complete solution

    let
	Source = #table
	(
		{"week.year"},
		{
			{"01.2018"},	{"02.2018"},	{"03.2018"},	{"01.2019"},	{"02.2019"},	{"03.2019"},	{"01.2020"}
		}
	),
	AddColumn = Table.AddColumn
	(
		Source,
		"isCurrentMonth",
		each Date.Month(DateTime.FixedLocalNow()) = Number.From(Text.Split(_[week.year],"."){0})
	)
in
	AddColumn

    Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy