Rolling Sum between two dates

[kasiaw29]

Hi community,

I'm hoping for someone's assistance on a measure I'm trying to create. 

I have a table with orders, their total value and date when it was created and invoiced, see below:

Let's use highlighted line as an example
Created 25/05/2021 and invoiced on 08/09/2021 of a value of 7,133.49. I want to create a measure where 7,133.49 shows in May, June, July and droppes of August so it almost rolls into each month from created until it's invoiced. 

I tried some measures using DATESBETWEEN or below:

Total Booked Open =

CALCULATE(SUM('Customer Orders'[BOOKED_VALUE]),

FILTER(

    'Calendar',

    'Calendar'[Day] >= MIN('Customer Order Dates'[Date Entered])

        && 'Calendar'[Day] <= MAX('Customer Order Dates'[Invoiced Date])))

But it's not working quite as expected. Any advice would be wonderful.

Thanks,

Kasia 

[amitchandak · Accepted solution]

@kasiaw29 , please refer if this way can help

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

new table approch

Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[kasiaw29]

Hey @amitchandak 

Had to modify it slightly so that it worked with what I was trying to measure but your article was 100% correct! Thank you! 

My measure, in the end, looked like this: 

Measure =

CALCULATE(

    SUMX(

        SUMMARIZE(

            FILTER(

                CROSSJOIN('Customer Order Dates','Calendar'),

            'Calendar'[Day] >= 'Customer Order Dates'[Date Entered] && 'Calendar'[Day] <= 'Customer Order Dates'[Invoice Date]),

        'Customer Order Dates'[Date Entered],'Customer Order Dates'[Invoice Date], 'Customer Order Dates'[Booked Value]),

        'Customer Order Dates'[Booked Value]))