cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Clara Regular Visitor
Regular Visitor

How to show full weeks in calendar visual (and not just days in selected month)?

Hello! I think my problem is fairly simple (but my knowledge only goes so far):

I have a calendar visual which is a matrix that looks like this:

 

Anotação 2019-07-11 163753.png
I have week numbers ("Semana") on rows, weekdays ("DiaSemana") on columns and month day ("DiaMes") as values. DiaMes is a measure and it looks like this (I've translated my column names for the sake of clarity):

DiaMes = 
var minweek = CALCULATE(MIN(Table1[WeekNum]),ALLEXCEPT(Table1,Table1[Date].[Month]))
var monthend = LASTDATE(Table1[Date])
var maxweek = CALCULATE(MAX(Table1[WeekNum]),Table1[Date]=monthend)

return CALCULATE(DAY(MIN(Table1[Date])),FILTER(Table1,Table1[WeekNum]>=minweek && Table1[WeekNum]<=maxweek))

Unfortunately, it doesn't work as I expected. I wanted the calendar to return full weeks even if some days fall out of the month in my slicer. What am I doing wrong?

 

calendar.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to show full weeks in calendar visual (and not just days in selected month)?

Ahh, sorry.  I didn't realize you were using MIN to get the current day's value. That filter expression returns the entire month that you want to show, so the min date will always be the 1st.  Like most tricky issues with DAX, the fix for this is more complicated than it first appears.

 

I was playing around with this, and the issue is that your year/month filters are applying to the matrix.  So the data the matrix itself is able to use is filtered.  What you want is basically something like this timeline slicer, but that shows the user months and slices other visuals by VALUES(dimDate[WeekNo]).

 

I was eventually able to get it to display the correct days in a matrix, but the solution is nowhere NEAR elegant.

First, I created a table called MonthWeekCombo.  I made sure to NOT relate it to the original dimdate table.

MonthWeekCombo = SUMMARIZE(dimDate, [Month Name], [Month], [WeekNum])

Then I added a slicer based on [Month Name] from MonthWeekCombo.  I created this measure [DisplayDay], for reasons that will become apparent soon.

DisplayDay = CALCULATE(SELECTEDVALUE(dimDate[Day]), FILTER(dimDate, dimDate[WeekNum]=SELECTEDVALUE(MonthWeekCombo[WeekNum])&&dimDate[Day Name]=SELECTEDVALUE(dimDate[Day Name] )))

Then I added a matrix to the report, with rows based on MonthWeekCombo[Weeknum], Columns based on dimDate[Day Name], and values based on the measure [DisplayDay].  I couldn't just use dimDate[Day] because the matrix needs it summarized, and I purposefully set it so that none of the filters on the MonthWeekCombo table apply to dimDate fields.


It's messy, but it does get the job done. 

snipa.PNG

5 REPLIES 5
Clara Regular Visitor
Regular Visitor

Re: How to show full weeks in calendar visual (and not just days in selected month)?

Maybe it is easier to explain it like this: I want the month slicer to filter WEEKS, but not DAYS.

 

i.e: When filtering January, my matrix should show all weeks that fall in january (weeks 1 to 5), but not only days that fall in january (all days from dec. 31, 2018 to feb. 3, 2019 should appear).

Super User
Super User

Re: How to show full weeks in calendar visual (and not just days in selected month)?

So this is a cool bit of DAX, but I'm not sure why you aren't using a previously created calendar visual?

 

Anyway, here's how I would change your DAX:

DiaMes = 
var minweek = CALCULATE(MIN(Table1[WeekNum]),ALLEXCEPT(Table1,Table1[Date].[Month]))
var monthend = LASTDATE(Table1[Date]) var maxweek = CALCULATE(MAX(Table1[WeekNum]),Table1[Date]=monthend) return CALCULATE(DAY(MIN(Table1[Date])),FILTER(ALL(Table1),Table1[WeekNum]>=minweek && Table1[WeekNum]<=maxweek))
Clara Regular Visitor
Regular Visitor

Re: How to show full weeks in calendar visual (and not just days in selected month)?

@Cmcmahan I tried a couple, but they didn't attend to my needs, I guess. I wanted to be able to show (and select/filter by) week number, and I guessed I could achieve this by simply selecting the rows in a matrix.

 

Also, thanks for your suggestion! I was absolutely sure it would work, only...

Anotação 2019-07-12 110052.png
(yellow shading in that one cell is due to conditional formatting)
I'm so confused

Super User
Super User

Re: How to show full weeks in calendar visual (and not just days in selected month)?

Ahh, sorry.  I didn't realize you were using MIN to get the current day's value. That filter expression returns the entire month that you want to show, so the min date will always be the 1st.  Like most tricky issues with DAX, the fix for this is more complicated than it first appears.

 

I was playing around with this, and the issue is that your year/month filters are applying to the matrix.  So the data the matrix itself is able to use is filtered.  What you want is basically something like this timeline slicer, but that shows the user months and slices other visuals by VALUES(dimDate[WeekNo]).

 

I was eventually able to get it to display the correct days in a matrix, but the solution is nowhere NEAR elegant.

First, I created a table called MonthWeekCombo.  I made sure to NOT relate it to the original dimdate table.

MonthWeekCombo = SUMMARIZE(dimDate, [Month Name], [Month], [WeekNum])

Then I added a slicer based on [Month Name] from MonthWeekCombo.  I created this measure [DisplayDay], for reasons that will become apparent soon.

DisplayDay = CALCULATE(SELECTEDVALUE(dimDate[Day]), FILTER(dimDate, dimDate[WeekNum]=SELECTEDVALUE(MonthWeekCombo[WeekNum])&&dimDate[Day Name]=SELECTEDVALUE(dimDate[Day Name] )))

Then I added a matrix to the report, with rows based on MonthWeekCombo[Weeknum], Columns based on dimDate[Day Name], and values based on the measure [DisplayDay].  I couldn't just use dimDate[Day] because the matrix needs it summarized, and I purposefully set it so that none of the filters on the MonthWeekCombo table apply to dimDate fields.


It's messy, but it does get the job done. 

snipa.PNG

Clara Regular Visitor
Regular Visitor

Re: How to show full weeks in calendar visual (and not just days in selected month)?

@Cmcmahan Thank you so much! Worked perfectly for what I needed

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 992 guests
Please welcome our newest community members: