Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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. 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

 

1 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

 

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

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
Not applicable

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

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors