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

[Clara]

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:

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?

[Cmcmahan]

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]

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

(yellow shading in that one cell is due to conditional formatting)
I'm so confused

[Cmcmahan · Accepted solution]

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. 

[Clara]

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

[Clara]

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