Select Month based on Year Selection from Slicer

[PowerBuddy1]

I have two slicers - Year and Month.

1st line chart: shows Month wise sales for the selected year

2nd line chart: shows daily sales based on selection of year and month. A message is displayed if month is not selected and no values are displayed on the chart.

Issue: 

1) If only year is selected and month is not selected, I want the current month value to be displayed in the 2nd line chart.

2) When the report is open, by default, current year and current month values should be displayed. I have tried using relative date but it then remains fixed and does not change if user selects year or month from slicers.

Please advise.

@amitchandak  @lbendlin @TheoC 

[PowerBuddy1 · Accepted solution]

Hi all,

Solved it! Thanks all for your help!

Below is what I did.

Filtered Months = ISFILTERED('Calendar'[Month])

This will check if Month is selected or not and return True / False

For values measure: 

Daily_Final_ = IF([Filtered Months] = FALSE(),CALCULATE('Calculations'[Total_Yield],'Calendar'[Month_No] = MONTH(NOW())),'Calculations'[Total_Yield])

This gives me current month values if month is not selected.

Now if i select Year and month is not selected, it gives me current month values. 

I have removed the month slicer and allowing the user to select month from the 1st line chart which displays month wise details. 

[lbendlin]

@PowerBuddy1 you need to be more specific. "Current" has different meanings in Power BI. It usually refers to filter context, not to "the month of the date of when the user is looking at the report".

Follow @johnt75 's advice. Of course all that will only work in import mode with frequent dataset refresh, and it is guaranteed to confuse your users .

[v-janeyg-msft]

Hi, @PowerBuddy1 

Did you refer to @johnt75  opinions? Have you implemented it yet? Has the problem been solved? If yes, you can  Accept it as the solution to help the other members find it more quickly.

If not, I would like to ask you:

1) If only year is selected and month is not selected, I want the current month value to be displayed in the 2nd line chart.

---

What if year and month are not selected? Do these two visuals put separate year and month columns or a hierarchy of dates?

2) When the report is open, by default, current year and current month values should be displayed. I have tried using relative date but it then remains fixed and does not change if user selects year or month from slicers.

---

Reports have no way of distinguishing between 'just opened' and 'after opening'. You can only define manually, 

There are more than one method mentioned above, but they are all workarounds, and there is no way to directly change. If you want to try something else, please answer my question first.

Best Regards,

Community Support Team _Janey

[PowerBuddy1]

@v-janeyg-msft 

Find below answers: 

What if year and month are not selected? Do these two visuals put separate year and month columns or a hierarchy of dates?

Year and month has to be selected. Year drop down is a radio button. 

1st Visual x-axis is Month

2nd Visual x-axis is Day

2) When the report is open, by default, current year and current month values should be displayed. I have tried using relative date but it then remains fixed and does not change if user selects year or month from slicers.

Can you pls advise on the workaround solution.

[v-janeyg-msft]

Hi, @PowerBuddy1 

Without data and samples, it is difficult for me to give a specific plan.

Your request is contradictory. There can be no such perfect solution.

Workaround1:

• Don't select the year and month in the slicer, then you can use the isfilter() function to control two situations, the current year and month are displayed when not selected, and the normal display after selection.

Workaround2:

• Set a selected column alone, including yes and no, and then use the isfiltered function to control when no, no matter what the year and month slicer selects, the current year and month are displayed, and when yes, the year and month selected by the slicer are displayed.

If you can understand what I mean, you can try it. If you have problem, Please share a simple file for me to test.

Due to personal reasons, I won't be able to get back to you until after the vacation is over.

Best Regards,

Community Support Team _Janey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

For the first question, presumably you are using SELECTEDVALUE in your measure to work out which month has been chosen. You can pass a 2nd parameter to SELECTEDVALUE which is returned if nothing has been selected, e.g.

var chosenMonth = SELECTEDVALUE('Date'[Month], MONTH(TODAY())

The 2nd question is something I've struggled with in the past. The only way I found to solve the problem was to add a new calculated column to my Date table which returns the string "Latest" if the row is in the latest period, otherwise it returns a text representation of the period. If you don't already have one, you will also need to add a column suitable for sorting at the desired granularity ( quarter, month, day ) and set your new calculated column to be sorted by that.

Once you have that in place, you can use the new calculated column in filters and slicers, sorted descending, and "Latest" will always appear at the top of the list.

[PowerBuddy1]

@johnt75 tried but not working.

If i include "Latest" in the drodpwn then in the year slicer only current year is visible. Need all the years to be mentioned.

[johnt75]

You may need to add multiple columns, one for each desired level of granularity. For example, you could have a "Reporting Year" column, which showed "Latest" for the current year and then the year number for previous years, and a "Reporting Month" column which showed "Latest" for the current month and the text or numeric representation you want for other months. 

You would also need a sort column for each of the new columns you add. A sort column for month would be tricky, you'd have to build some logic around the current month number I think