aggregation table only within last 12 months or last year

[kadirkkkk]

Hello there,

It's a question about the aggregations on Model tab of PowerBI desktop (manage aggregations)

I have a fact table with ten years of data but most of time i use last 12 months to show in visuals. I do not want to aggregate all years to save both space in GB's and powerbi service refresh time.

In my scenario if it's a visual of last 12 months then it should use the agg. table but if it's an old data to visualize than it should use the fact table (end user can wait if it's old data, no problem)

Notes:

* I don't want to dublicate the fact table (one for last 12 months and the other is for the old data) and measures.

* I just want one fact table and one agg table.

* I can not use incremantal refresh cause of database type.

[kadirkkkk]

I think i'll prepare two DQ tables. One is (present table) filtered with last 12 months and the other is (olds table) filtered older than 12 months. then aggregate the present table, not the olds table. And prepare measures with if clauses over 12 months. if it's newer then 12 months then get the value of present table, else olds table value. But in this case i must dublicate all measeures i need for the both present and olds table 😞

[lbendlin]

"

• I just want one fact table and one agg table.
• I can not use incremantal refresh cause of database type.

"

Then you're stuck. Your aggregation table will need to aggregate across the entire direct query table.