Forum Discussion

Resolver I

6 years ago

Cumulative Values between 2 Dates

I have a Dimension Table, Fact Table and Date Table. In the Dimension Table you have a ProjectNr, its Price, DateSigned and ProjectEndDate. In the Fact Table you see when you spend on this Pro...

Need Help

Show and Tell

Anonymous

Not applicable

6 years ago

JohanData

If I get you right, you could use RELATED function to compare the dates in fact table with dates dimention table.

cumulative spending by project no.=
CALCULATE (
    SUM ( 'Fact'[SpentAmount] ),
    FILTER (
        ALLEXCEPT ( 'Fact', 'Fact'[ProjectNr] ),
        [Date] <= EARLIER ( 'Fact'[Date] )
    ),
    'Fact'[Date] >= RELATED ( 'Dimension'[DateSigned] ),
    'Fact'[Date] <= RELATED ( 'Dimension'[ProjectEndDate] )
)

Paul
Best
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.