Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Donaldo_
New Member

Total monthly Total monthly amount

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

DATEAmount
2024-07-01500
2024-08-011000
2024-09-01800
2024-10-01700

 

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?

3 REPLIES 3
v-zhangtin-msft
Community Support
Community Support

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)))

vzhangtinmsft_0-1727749577624.pngvzhangtinmsft_1-1727749592989.png

 

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
Solution Specialist
Solution Specialist

@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

sanalytics_0-1727695066840.png

 

Attached the pbix file for your reference

https://we.tl/t-xZUmjUjXbR

 

Hope it helps

 

Regards

sanalytics

 

 

bhanu_gautam
Super User
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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.