Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
@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
Hope it helps
Regards
sanalytics
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
Proud to be a Super User! |
|
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
19 | |
15 | |
14 |