Stacked value with a start and end date

[Marrtine_240]

Hi,

I have a table that contains, among other things, a start and an end date that has a certain value between those dates. In a visual in Power BI, I would like to see the sum of all lines of that value if it falls between the start and end dates.

Does anyone have an idea how I can solve this? Or is it described somewhere?

Below the table and the desired outcome.

Thanks!

Rij	Startdatum	Einddatum	Waarde
1	1-8-2022	24-10-2022	0,6
2	1-9-2022	01-10-2022	0,3
3	1-9-2022	01-10-2022	0,7
4	15-11-2022	31-12-2022	0,6

Desired outcome.jpg

[Marrtine_240 · Accepted solution]

Hi @Anonymous ,

Thank you so much for the push in the right direction. Now I will definitely be able to finish my model. I had tried a lot, but now I see why it didn't work. I already had it working, but via an extra step in Excel, this can now be removed.

Martine

[Anonymous]

Hi @Marrtine_240 ，

Please have a try.

Create a calendar date. Then merge the 2 tables.

From the “Get data” drop-down menu, select “Blank Query”. This will open the Power Query Editor window where you will build the table.

Create a list of dates using the formula bar. 

= List.Dates(#date(2022,8,1),153,#duration(1,0,0,0))

Then convert the List into a table by clicking the “To Table” button in the Convert menu on the ribbon. You will not make any selections r in the subsequent window, click “OK”.

Once the List has been converted to a Table, rename the column to “Date Value” and change the data type to a Date type using the data type button next to the column header.

More details about How to Create a Date Table 

Then merge the two tables.

Then expand the column.

And change the calendar column type to date.

Finally create a measure.

Measure = 
VAR actived =
    CALCULATE (
        SUM ( Merge1[Waarde  ] ),
        FILTER (
            ALLSELECTED ( Merge1 ),
            Merge1[Calendar.date] <= MAX (Merge1[Calendar.date] )
                && Merge1[Attribute] <> "Einddatum"
        )
    )
VAR Einddatum =
    CALCULATE (
        SUM ( Merge1[Waarde  ]  ),
        FILTER (
            ALLSELECTED ( Merge1 ),
             Merge1[Calendar.date] <= MAX (Merge1[Calendar.date] )
                && Merge1[Attribute]  = "Einddatum"
        )
    )
RETURN
    actived - Einddatum

Best Regards

Community Support Team _ Polly

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