Display data based on a condition on year/month column

[Anonymous]

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. containing something like 02.2018 or 03.2019 etc etc as text)

What I want to do: IF the current month(system time) is January then allow the column to have the values of 2018 (current year-2), 2019 (current year-1), and 1st month of 2020(current year) ELSE allow the column to have the values of 2019 (current year-1) and 2020 (current year)

Is there any way to make this happen?

I have already tried a few alternatives like trying to keep a report filter but the problem is I need this to be dynamic and not static assigning the value like 2018,2019 or so will not work

Any suggestions? 

Would really appreciate the help

[Jimmy801]

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]

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 · Accepted solution]

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