Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
significant
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
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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.

View solution in original post

3 REPLIES 3
theov
Advocate II
Advocate II

Hi there, in this video this subject is well explained : 

steps : creation a copy of calendar > inactive realtionship > measure with DATESINPERIOD

https://www.youtube.com/watch?v=QQhnj3_Rvlo

v-yulgu-msft
Microsoft Employee
Microsoft Employee

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.

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...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.