Aggregated Tables for weekly, monthly (etc) granularity

[CharleyKirton]

Hi all!

I'm a bit late to the aggregated table party and feel there's something missing in my understanding of it, so I'm having a mare and really need some help!

I need to aggregate my fact table to weekly; monthly; quarterly and annual aggregations.  I'll explain the daily and weekly tables here:

I have a direct query fact table at a daily granularity, which has:
> Date  (This relates to a date table in dual storage mode)

> Day Type  (This relates to a DayType dim table in dual storage mode)

> Person ID

> Person ID-EndOfMonth Key  (This relates to a person attribute dim table in dual storage mode)

> Metric

Then I have an imported aggregated table at a weekly granularity which has:

> Date (as week ending)

> Person ID

> Person ID-EndOfMonth Key

> Metric

I am mapping the aggregated table fields as follows:

> Date (as week ending) group by DetailsTable[Date]

> Person ID group by DetailsTable[PersonID]

> Person ID-EndOfMonth Key group by DetailsTable[Person ID-EndOfMonth Key]

> Metric sum DetailsTable[Metric]

On my line chart in the report I have 

X-Axis = Date (from Dim Date table)

Y-Axis = Measure as a simple SUM(DetailsTable[Metric])

I can only get a line showing on my line chart when I filter by DayType, which makes sense to me, but I can't get it to show anything at the weekly level.  Can anyone highlight where I'm going wrong, please?

What I need is for the user to select from "daily/weekly/monthly/quarterly/annual" options and it display the relevant aggregation.  

FYI - The need for speed in the report is what's driving this request.  Currently, there are more fact tables (all imported) in the model, 2 of which are 300m rows, so currently it takes 30 seconds to load the annual line chart and just over a minute for the daily line chart in this version.  The hope is that aggregated tables will make all bar the daily grain a lot snappier.

Thanks very much for your help (in advance!!)   😊

Charley

[lbendlin]

Your aggregation table has to contain a subset of the columns of your details table. You can only use simple (implicit) measures.

How Aggregation Tables Improve Performance for Power BI Reports (corebts.com)

[CharleyKirton]

Yeah, I only wanted to SUM, but the problem is wanting to do it on a date range to avoid calculating at daily granularity where I don't need to.  The desire is for daily data to be available, but I doubt it will get used as much as weekly or monthly. 

I can see how to set aggregations up with less dimensions, but my understanding is getting something wrong as I can't figure how it would work with dates... should I add extra columns for End of Week and End of Month to the raw set to achieve a grouped subset in my aggregated tables ... but in doing that, do I still map to the date column in the raw??  

I've been going round in circles on this for a while and due to the nature of the problem, it takes ages to find out your latest idea isn't working.  😬

[lbendlin]

if you want weekly and monthly aggregates (shudder) then you will probably need to also aggregate your calendar table.