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

 

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

     

3 Replies

  • 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