Switch between weekly and monthly Views

[Diego-mx]

I have a Calendar table, which has at least three columns:  a_date, week_date, month_date; the last two corresponding to the beginning of the corresponding week and month.  

So far, I've showed my reports grouping by month_date.  And then I duplicate the page, to show them via week_date.  

However, I have two main type of reports:  KPIs and Historical.  Thus duplicating starts multiplying the effort of modification.  It would be better if I could just add a slider that would choose what to group by.  

In any case, I add one indicator table with "week" and "month" values to choose from. 

Solution 1. 

Ideally I could link the slider to the corresponding column and then whenever I choose one all the rest groups accordingly. 

Solution 2. 
I create a secondary grouping calendar table with "long" format (as opposed to wide format), with columns "b_date" and "period" that looks like this: 

b_date      period 
7/1/2018   "month"
8/1/2018   "month"
9/1/2018   "month" 
... 
7/1/2018   "week"
7/8/2018   "week"
7/15/2018  "week" 
....

and this becomes easier to link but then I don't know how to create this table as a union of two tables with DAX syntax. 

If anyone can help me with any of the solutions I'll appreciate it.  

Thanks. 

[v-yulgu-msft · Accepted solution]

Hi @Diego-mx,

Solution 2. 
I create a secondary grouping calendar table with "long" format (as opposed to wide format), with columns "b_date" and "period" that looks like this: 

b_date      period 
7/1/2018   "month"
8/1/2018   "month"
9/1/2018   "month" 
... 
7/1/2018   "week"
7/8/2018   "week"
7/15/2018  "week" 
....

and this becomes easier to link but then I don't know how to create this table as a union of two tables with DAX syntax. 

---

You can create a calculated table as below:

CalendarTable2 =
UNION (
    SELECTCOLUMNS (
        CalendarTable1,
        "b_date", CalendarTable1[week_date],
        "period", "Week"
    ),
    SELECTCOLUMNS (
        CalendarTable1,
        "b_date", CalendarTable1[month_date],
        "period", "Month"
    )
)

Best regards,

Yuliana Gu

[marclelijveld]

@v-yulgu-msft 

Can you please explain what the advantage of the calculated table is? Bookmarks is easier, isn't it... 

Thanks, 
Marc

[marclelijveld]

Hi @Diego-mx

You can create a hierachie in your visual. Add both week and month on the axis, and there will be a small icon in the visual header to change the context to week or month. Unfortunatly this will only impact that specific visual. 

An other solution could be using bookmarks. So first add week and month at all visuals like explained above. Then open the bookmarks pane in Power BI desktop. Create a bookmark with all visuals on weekly base and create a second bookmark with all visuals based on month. Then you can add a button (see upper bar) which you can assign to the created bookmarks. Now the user can switch dynamicly to week and month themselves. 

Will this work for you? 

- Marc

[Diego-mx]

Thanks. That's a third solution.   

This is one solution I was avoiding because I feel suspicious of the hierarchies, and haven't really understood how they work. 
But this is one good reason to try them.  
I'll come back to upvote this.  

Thanks.