Total monthly Total monthly amount

[Donaldo_]

There is a table where you have entered the monthly amount.

DATE	Amount
2024-07-01	500
2024-08-01	1000
2024-09-01	800
2024-10-01	700

For example, if you look up the date from 2024-08-15 to 2024-09-15, I need to get 1800 values.

What should I do?

[v-zhangtin-msft · Accepted solution]

Hi, @Donaldo_ 

You can try the following method. Create a new date table.

Date = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

Measure = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[DATE]>=EOMONTH(MIN('Date'[Date]),-1)+1&&[DATE]<=EOMONTH(MAX('Date'[Date]),0)))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

[sanalytics]

@Donaldo_ 
Follow the below logic
1) Generate List of Dates between StartDate and EndDate in PQ. You can do this DAX as well. but i did it in PQ.
2) Create a small DAX to show the amount.

For Step 1.

List.Dates
(

    [Date],
    Number.From(
[EndofMonth]

    ) - Number.From
    (
      [Date]
    ) + 1 ,#duration(1,0,0,0)
)

For Step 2

TotAmount = 
SUMX(
     VALUES(Source[Date] ),
     CALCULATE(
        MAX( Source[Amount] )
     ) )

Below screenshot

Attached the pbix file for your reference

https://we.tl/t-xZUmjUjXbR

Hope it helps

Regards

sanalytics

[bhanu_gautam]

@Donaldo_ , 

You need to create a measure that sums the Amount for the specified date range. You can use DAX (Data Analysis Expressions) to create this measure.

TotalAmountInRange =
VAR StartDate = DATE(2024, 8, 15)
VAR EndDate = DATE(2024, 9, 15)
RETURN
CALCULATE(
SUM(Table[Amount]),
Table[DATE] >= StartDate,
Table[DATE] <= EndDate
)
Use the measure in your report: You can now use this measure in your Power BI report to display the total amount for the specified date range.

Currently it is hardcoded you can change var as per your logic