Hello,
I have a data model that is
DirectQuery:
FactSales -> Sales Order transactions
Import:
FactSalesAgg -> Referencing FactSales, Group By on our DimAllTime_Key and SUM of LINEAMOUNT
*This import aggregated table then has 'Managed Aggregations' applied in order to act as the aggregate table for FactSales*
Dual
DimAllTime -> Calendar table (disconnected) that includes our fiscal calendar information
DimAllTime_InvoiceDate -> Calendar table joined to FactSales and FactSalesAgg on DimAllTime_Key = DimAllTime_Key (1:* relationship)
In our report we have users select a date from the DimAllTime calendar. We then use DAX measures to get the date selected + the related date last year (based on our fiscal dimensions)
Example for TY
Sales TY =
CALCULATE(
SUM(FactSales[LINEAMOUNT]),
DATESBETWEEN(DimAllTime_InvoiceDate[CALENDARDATE], [DateFrom_TY], [DateTo_TY])
)
DateFrom_TY =
CALCULATE(
FIRSTDATE( DimAllTime[CALENDARDATE] ),
ALLSELECTED( DimAllTime[CALENDARDATE] )
)
DateTo_TY =
CALCULATE(
MAX(DimAllTime[CALENDARDATE]),
ALLSELECTED( DimAllTime[CALENDARDATE] )
)
Plus equivalent three DAX measures for LY.
Sales LY =
CALCULATE(
SUM(FactSales[LINEAMOUNT]),
DATESBETWEEN(DimAllTime_InvoiceDate[CALENDARDATE], [DateFrom_LY], [DateTo_LY])
)
DateFrom_LY =
LOOKUPVALUE(DimAllTime[CALENDARDATE], DimAllTime[DayYear],
LOOKUPVALUE(DimAllTime[YEAR_DAY_IN],DimAllTime[CALENDARDATE], FIRSTDATE(DimAllTime[CALENDARDATE])) &"-"&
LOOKUPVALUE(DimAllTime[FISCALYEAR],DimAllTime[CALENDARDATE], FIRSTDATE(DimAllTime[CALENDARDATE]))-1
)
DateTo_LY =
LOOKUPVALUE(DimAllTime[CALENDARDATE], DimAllTime[DayYear],
LOOKUPVALUE(DimAllTime[YEAR_DAY_IN],DimAllTime[CALENDARDATE], LASTDATE(DimAllTime[CALENDARDATE])) &"-"&
LOOKUPVALUE(DimAllTime[FISCALYEAR],DimAllTime[CALENDARDATE], LASTDATE(DimAllTime[CALENDARDATE]))-1
)
My expectation is that these Datefrom/DateTo calculations would be able to pass the expected DimAllTime_Key (from our DimAllTime tables) and hit the POwer BI aggregated table. Instead it appears to hit the DirectQuery - presumably because of the disconnected DimAllTime table being used as like a dynamic parameter.
Any idea how we can replicate this functionality - allowing a users selection to actually select multiple dates (for this and last year, or this and last time period X) while taking advantage of the imported aggregate table?
Both our DimAllTime tables follow a format like:
| DimAllTime_Key | CALENDARDATE | FiscalYear | DayInYear |
| 368 | 12/30/2019 | 2020 | 1 |
| 369 | 12/31/2019 | 2020 | 2 |
| 370 | 1/1/2020 | 2020 | 3 |
| 1 | 12/31/2018 | 2019 | 1 |
| 2 | 1/1/2019 | 2020 | 2 |
| 3 | 1/2/2019 | 2020 | 3 |
FactSales is similar to:
| DimAllTime_Key | DimX_Key | DimY_Key | DimZ_Key | LineAmount |
| 1 | 23124 | 231234 | 12354 | 100 |
| 3 | 2391 | 124 | 12354 | 200 |
| 3 | 65843 | 3943 | 12354 | 300 |
| 3 | 23124 | 231234 | 12354 | 50 |
| 3 | 2391 | 124 | 12354 | 350 |
| 370 | 65843 | 3943 | 12354 | 25 |
| 370 | 23124 | 231234 | 12354 | 100 |
| 3 | 2391 | 124 | 12354 | 200 |
| 2 | 65843 | 3943 | 12354 | 300 |
| 368 | 23124 | 231234 | 12354 | 50 |
| 239 | 2391 | 124 | 12354 | 350 |
| 40 | 65843 | 3943 | 12354 | 25 |
FactSalesAgg is similar to
| DimAllTime_Key | LineAmount |
| 1 | 100 |
| 2 | 300 |
| 3 | 1100 |
| 368 | 50 |
| 239 | 350 |
| 370 | 125 |
| 40 | 25 |
