Create attribute for week/month/quarter to use with multiple measures in matrix

[Meagan]

I'm trying to recreate this table in Power BI: 

I have measures for lost customer, inactive customer, and new customer. I also have measures for lost customer - week, lost customer - month, lost customer - quarter, inactive customer - week, inactive customer - month, inactive customer - quarter, etc. Based upon the date in the visual filter, the first column should return the total for the selected week, selected month, and selected quarter. 

I'm creating reports in the web service and trying to find a creative way to create this chart (made in Excel) in Power BI. There is no One Drive for Business or SharePoint team site available so I can't just pin the chart from Excel to my Power BI dashboard.

I tried multi-row cards, but the lack of ability to control the number of items and the truncation of category labels makes it a poor option. I tried creating one table per row, but it leaves way to much white space when I pin the tables to a dashboard.

I'm hoping there is some creative DAX (maybe a disconnected table?) that I could create to make the week/month/quarter values into a single attribute by which I could then filter my measures. But I'm not seeing how that is possible. 

[Meagan · Accepted solution]

I ended up solving my own problem and blogged about it here. I already had the date table and the base measures. So I added a disconnected table that would let me put the metrics in an atrribute so they could go on rows. Then I added some calculated measures to use the disconnected table. I made one measure for weekly, one for monthly, and one for quarterly and placed them in Values.  

I think Power BI should be able to accomodate putting measures on rows in a matrix. Please vote for this feature request here. 

[fbrossard]

What kind of aggregation do you want ? Week-To-Date, Month-To-Date, Quarter-To_Date) ?

I think it should be nice to create a rich time dimension a create calculated measures on it.

To generate a Time Dimension with Power Query you can use this 

• http://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/ 

You can also modify the script to enrich you time dimension by adding relative attributes (relative week (W0, W-1 ....), relative months, blablabla,....), here are some references :

• http://blog.crossjoin.co.uk/2013/01/24/building-relative-date-reports-in-powerpivot/
• http://fbro.wordpress.com/2013/02/20/implementation-dune-datetool-avec-powerpivot

[Greg_Deckler]

Any chance you can post some sample data? Is the "selected date" a date-based slicer for example, so something like "1/6/2016" or is it something else? I guess that is the main question. I am assuming that in your customer table, you have a date field as to when that customer was lost, went inactive or became a new customer.

I would think that a matrix coupled with some custom columns/measures should be able to get you what you want but I need a little more information.