Aggregate facts where the date is between a start & end date in a related dimension.

[mlmchilli]

Hello!

I'm hoping somebody can point me in the right direction.

I have a budget dimension with start & end date attributes. I have a fact table with events at day level that are related to budgets. I want to be able to aggregate the fact table at budget level where the date of the event in the fact table falls between the start and end dates in the budget dimension.

This is the DAX I have in place now which appears to work on an individual day level but not aggregated. 

The table visual shows the Budget ID & Start/End dates from the dimension with the aggregated revenue. If I add in the individual days from the Calendar dimension I can see that the DAX is working for the 18 & 19th as they are outside of the budget dates, however, the total is calculated incorrectly. This is the same in the bottom table where I have removed the individual days to aggregate to Budget level and the DAX doesn't appear to work correctly here. 

Any assistance on this would be greatly appreciated!

Many thanks!

[Ashish_Mathur]

Hi,

I'd suggest that you write calculated column formulas in the FactDelivery table to bring over Budget_Start and Budget_End columns from the DimBudget table.  To your visual, drag Budget_Start and Budget_End columns from the FactDelivery table.

[mlmchilli]

Hello @Ashish_Mathur  Many thanks for your suggestion. The Fact table is over a billion rows and growing. Would this be a concern? Is there a benefit to your suggestion over the suggestion from @hnguy71 ? Just trying to understand both angles here.

[Ashish_Mathur]

You are welcome.  Please try each suggestion on your live data file and let us know.

[hnguy71 · Accepted solution]

@mlmchilli , seems like you may have incorrect grand totals. Try this instead:

RevenueBugdetToDate := 

VAR _Total = 
SUMMARIZE(
    DimCalendar, 
    DimCalendar[FullDate], 
    "@Value", 
        CALCULATE(
            SUM(FactDelivery[Revenue]),
            FILTER(
                DimBudget, 
                DimBudget[Budget_Start] <= MAX(DimCalendar[FullDate]) &&
                DimBudget[Budget_Start] >= MIN(DimCalendar[FullDate])
                )
        )
)

RETURN SUMX(_Total, [@Value])

[mlmchilli]

Apologies for the delay on this... **bleep** Covid! It works well. Many thanks!

[mlmchilli]

Hello @hnguy71 Many thanks for your suggestion. I had to change the Dax a little as you ghad BudgetStart twice but all looks good. Will test in production and see what happens.