Forum Discussion

JohanData's avatar
JohanData
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 Project (key is ProjectNr, Date and Spent Amount).

 

I'd like to see: cumulative spending values on each project, where the DateTable >= DateSigned and DateTable <= ProjectEndDate

 

File: https://1drv.ms/u/s!AvDjQoL_zmjliFW4jNoEerXTDhOu?e=0wEDfi 

 

 

Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    JohanData 


    You may delete or merge this post, I will keep follow up on your new post.

     

    Paul