Forum Discussion
Total monthly Total monthly amount
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?
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.
3 Replies
- bhanu_gautam
Super User
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
sanalyticsDonaldo_
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
Hope it helps
Regards
sanalytics
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.
