Calculate number of units between multiple dates

[rolf1994]

Hi,

I have the following table. For every month there is one line that contains the amount of Units for the period (month). Type = 'Service':

Start date	End date	Units	Type
1-jan	31-jan	32	Service
29-jan	31-jan	-1	Adjustment
1-feb	28-feb	31	Service
1-mrt	31-mrt	31	Service

When there is an adjustment during the month a new line gets created with the change in quantity. i.e. on the 29th of january the quantity is decreased by 1. Type = 'Adjustment'

I want to create an area chart that shows me 32 units from the 1st of january untill the 28th of januari and 31 units from the 29th of january. Something like this:

Date	Units
1-jan	32
2-jan	32
3-jan	32
…	32
29-jan	31
30-jan	31
31-jan	31
1-feb	31

Would that be possible?

[v-jiascu-msft · Accepted solution]

Hi @rolf1994,

Please check out the demo in the attachment. 

1. Create a date table if you don't have one.

2. Establish a relationship.

3. Create a measure.

Measure =
CALCULATE (
    SUM ( Table1[Units] ),
    FILTER (
        ALL ( 'Table1' ),
        MIN ( 'Calendar'[Date] ) >= 'Table1'[Start date]
            && MIN ( 'Calendar'[Date] ) <= 'Table1'[End date]
    )
)

Best Regards,

Dale