Conditional display of imported data based on dates

[Anonymous]

Hi all,

For my current power bi file I am importing data of three years and need to change the display of data based on changing to a new year...

So basically if the last refresh is done in months 2-12, display the data for last year(2018), this year(2019) and next year(2020). 

But if the refresh is done in the first month of the year then change the year metrics and display data as last year(2019), this year(2020) and next year(2021)

Idea: automate the display of data when year changes

Approach: Needs to be done on the Power Query side but setting filters doesn't seem to make the cut for automation

Maybe someone has better insights. 

Thanks in advance. 

[amitchandak]

One of the ways is to use the relative date filter in filter pane or in slicer

On slicer also there is an option. You can choose this year or last year.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

This filter option is not much of use in this scenario. 

The main case is to deal with the yearly transition to reflect the changing year every January such that the metrics of 'previous year', 'current year' and 'new year' reflect the actual change in year. 

I am trying out the procedure of accessing current date with DateTime.LocalNow() in conjunction with IF condition to limit the data such as:

if the current month is January then select the rows where year is localyear, localyear-1 and localyear+1

else  select the rows where year is localyear, localyear-1 and 1st month of localyear+1

[Anonymous]

Hi 

Is it alot of data? over 5mil rows?

Okey,  have you tried the date/time filters in PowerQuery-

you can choose many or just add 3 fact tables(references), 

fact 1 with filter previous year

and so on.

And then append them into one table. (dont forget to press off "enable load" on the tables when you have one appended table.)

 

[Anonymous]

I did consider using the date filters but the column needs to be identified as date in order to use them.

The column in my data is month.year and is identified as text for the convinience of use for other calculations and so date filters will not work here. 

What I'm trying to do is: 

//accessing current date

datenow=Text.From(DateTime.LocalNow()),
yy=Number.FromText(Text.Range(datenow,6,4)),
mm= Number.FromText(Text.Range(datenow,3,2)),

//limiting data based on this

#"Custom1"= if(mm=1)then Table.SelectRows(#"Renamed Columns", each[Extracted Year]=yy)
else
Table.SelectRows(#"Renamed Columns", each[Extracted Year]=yy -1)

But the only problem is- I'm getting an empty table now. 

Can anyone suggest why that might be so?

[Anonymous]

Hi,

you need to create columns in your calendar "Previous year",  "This Year", "This Month" and so on.

for example:

This Month= IF('Calendar'[Monthnr] = MONTH(TODAY());"This Month";'Calendar'[Month])

This Year= IF('Calendar'[Year slicer] = YEAR(TODAY());"This Year";'Calendar'[Year])

Hope this can help you!

/Adam