cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
significant Frequent Visitor
Frequent Visitor

Show all months of year with one month selected

After implementing a solution with Power Query, a table, VBA and some other hacks I have a quite robus implementation to achieve following result.

However, if I would like to aquire the same result with Power Pivot / datamodel / DAX I struggle with the month filter.
The user should be able to select a month till which the actual are presented (limited to available data). However, planning should remain visible for the whole year.

If I implement a month filter, however, the graph gets narrowed down to the selected month.
I have tried to duplicate the calendar table, struggled with ALL, ALLEXCEPT but did not found a feasible result.

Advice as in:

https://community.powerbi.com/t5/Desktop/Show-whole-year-while-slicing-on-one-month-DAX/m-p/342122#M...

with two tables gives an "Relationships might be needed" error.

Who can point me in the right direction?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Show all months of year with one month selected

Hi @significant,

 

You should drag the [Month-Year] column from actual data table onto chart axis, rather than from calendar table.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Show all months of year with one month selected

Hi @significant,

 

You should drag the [Month-Year] column from actual data table onto chart axis, rather than from calendar table.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
significant Frequent Visitor
Frequent Visitor

Re: Show all months of year with one month selected

Super, thanks.

My measure now reads:

hoursRol:=CALCULATE(SUM(logData[hours]), DATESBETWEEN('Calendar'[Date],STARTOFYEAR('Calendar'[Date]),max('Calendar'[Date])))

edit:

@v-yulgu-msft

maybe you can provide an insight if we look at the days of a month specifically. With above approach, the rows always show till value 31. This of course only holds true in particular months.
How can it be arranged that the number of rows corresponds to the number of days of the selected month.

In my point of view, the reason for showing till 31 is due the fact that the actual data table is not filtered for months. Any suggestion...