Distributing values between start and end date

[peterso]

Hi everyone,

I'd like to evenly distribute values on a chart by month between the start and end date. I have a date table as well. Wondering if this is something that DAX can help with?

Thank you in advance

[JK-1]

Thank you for the insight here (on old query, with file example).

I have used the measure;

Measure_Paid = CALCULATE(SUMX(SUMMARIZE(filter(CROSSJOIN(PaidData,DatePAY),DatePAY[Date] >= PaidData[From] && DatePAY[Date] <= PaidData[To]),PaidData[ID],DatePAY[Date],PaidData[Amount],PaidData[From],PaidData[To]),DIVIDE(PaidData[Amount],DATEDIFF(PaidData[From],PaidData[To],DAY)+1)))

but am experiencing a problem when there is a credit and replacement payment. So in 2024 within the PaidData I have 3 transactions:

01/01/2024-31/12/2024 £1000
01/01/2024-31/12/2024 £-1000
01/01/2024-31/12/2024 £1000

The above seems to be ignoring one of the positives. It continues to show a net return of £0 paid for 2024.  X-axis graph

Tried to adapt measure but can't quite reach a valid execution. Have checked existing totals (new visualizations table) and using Sum in the build visual column dropdown it shows £1000 for the period, flick to Don't summarize it returns the £1000 and £-1000 as 2 entries, giving me £0 overall, but not the third line and final £1000.

Any tips gratefully received. At present this representation throws out £1000 as potentially outstanding with the SUMX(SUMMARIZE --what options would be available to tailor it for inclusion?  {Sorry - newbie here}

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.  Please also explain the question.

[JK-1]

I have the below (copied) as 'PaidData' table, with a further -[DatePAY] calendarauto() date table- using the Measure mentioned above within PowerBI. Using the graphs, stacked or lined, for 2024 the total value being reported is £0, and for 2025 £1500. The graph utilises the X and Y axis with just a

YEAR(DatePAY[Date]) in the DatePAY calendar to show the associated year.  So all in DAX

For 2024 the actual sum would be £1000 overall. But using the Measure it seems to have disregarded one of the 3 and can't tell what needs tweaked in the Measure. As I can only see £0 for 2024 this visually charts an untrue gap, versus what has been paid for 2024. So am trying to work out how the Measure can be improved, SUMX(SUMMARIZE -the filter would have the same ID but I don't know why its not totalling correctly. Thank you

'PaidData'

ID	From	To	Amount	Paid
10012	01/01/2024	31/12/2024	1000	20/12/2023
10012	01/01/2024	31/12/2024	-1000	03/01/2024
10012	01/01/2024	31/12/2024	1000	05/01/2024
10012	01/01/2025	31/12/2025	1500	19/12/2024

[Ashish_Mathur]

Hi,

Please refer to my solution of a similar problem in the attached file.

[amitchandak · Accepted solution]

@peterso , refer to this blog for measure way

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Attached file for New table way