show columns based on a condition

[diegoadum]

Hi folks, I'm working on a table that will show actual and plan for the current fiscal year. The idea is to find out the way to show only YTD info, which mean to show only columns from the beggining of the year until the current month. Here is the tricky part. the plan is fully loaded for the 12 periods, so if I want to compare Actual vs Plan for the periods that I have no actuals,  the variations are high and the whole table turns into red (I didn't mention that I have applied conditional formating to the table).

So, is there a way to show or hide columns for a table in PowerBi? Somethine like conditional show that exist in other tools?

Many thank in advance!!

[Anonymous]

Hi @diegoadum

You can achieve this by adding 2 columns in your calendar dimension (date dimension): 

- relative month &
- relative year

in the report you have to use two filters on report (or page) level:
relative year = 0
and
relative month <0

Feel free to ask if you need assistance with the formulas for relative month and relative year.

[Anonymous · Accepted solution]

For those who don't know the formula in Power Query:

relative Month:
(Date.Year([Date])*12+Date.Month([Date])) - 
(Date.Year(DateTime.LocalNow())*12+Date.Month(DateTime.LocalNow()))

relative Year:
Date.Year([Date]) - Date.Year(DateTime.LocalNow()) 

[diegoadum]

Hi @Anonymous, thank for the time to reply this. I think I got your suggestion and I'm going to take your offer to get some help with the formulas for the relative month and year!

Again, thank in advance!