Forum Discussion
Rolling Sum between two dates
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:
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-or-end-date/ba-p/1503785If 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
2 Replies
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-or-end-date/ba-p/1503785If 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-9ad6a84cf5f2Hey 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]))
