Calendar for current month and future 11 months

Power BI
1

Hi Brain Trust,
I am an amateur at Power BI and I am just starting on it. Basically, I am learning as I develop.

Q1
I have a requirement to build a report where it shows the current month and future 11 months. The report will always look at the current month and future months. There is no requirement to choose months or go to a past month.

Wonder how this can be achieved.

Q2
I have a category type which includes a whole lot of different types. For the dashboard, i need only two types, which i have chosen and restricted via a page filter. The category type has been added as a slicer. The two categories are “Cabin” and “Site”. I also want to have a category called “Combined” which is the addition of the two categories. I know that selecting both cabin and site will give the same answer but is it possible to have a new category type as well?

TIA.
Cheers

2

Current and future 11 months can be achieved with the addition of a month offset to your calendar table in M this would look something like:

InsertMonthOffset = Table.AddColumn(YourPreviousStepName, “MonthOffset”, each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(DateTime.FixedLocalNow()))) + Date.Month(Date.From(DateTime.FixedLocalNow()))))

If you add this in the (Power Query) Advanced Editor don’t forget to change the YourPreviousStepName and add a comma at the end if this is not the last line in your code.

Then you can add a Report- or Page filter where [MonthOffset] =0 and [MonthOffset] <12

Hope this helps.
Melissa

3

Yes nice one Melissa,

You can solve it in the date table quite easily with a new column. You could also do this with a calculated column as well.

For question 2 (in future can we place different questions in different forum posts)

This is quite easy as well. I think you just want to select both at the same time. I don’t think you really need to create anything new. The user should just be able to see that they can select multiple items in the slicer. This way they can have the flexibility to adjust it how they want. To me adding in a ‘combined’ dimension just doesn’t seem required.

Thank
Sam

For more tips on using the forum see below.

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30

4

Thank you Melissa.

5

Thanks. But there is a distinct requirement to have a separate row called Combined.

6

Have you thought about just creating another column or dimension that has the label combined?

This way you have a another selection that can be made, most likely from another slicer, that will enable you to or the user to quickly filter based on those requirements.

Sometimes it’s all about weighing up what makes sense to do inside of Power BI. Sometimes having such a strict requirement when the complexity to develop it makes the solution overly complicated, doesn’t really make sense to me.

The reality is with Power BI creating this sort of filter is seriously so simple Just by either multi selecting or creating a new dimension. So sometimes it’s better just to teach your users a slightly different way of doing things then making your development 5 to 10 times more difficult.

See how you go with this idea.

Sam

1 Like