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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Clara
Advocate II
Advocate II

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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

View solution in original post

5 REPLIES 5
Cmcmahan
Resident Rockstar
Resident Rockstar

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

@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

Cmcmahan
Resident Rockstar
Resident Rockstar

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

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

Clara
Advocate II
Advocate II

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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