Hi @v-monli-msft,
Thank you for the response,
Wouldn't that only show me the difference between 2 dates and return the value in hours(or other specified)?
I need to return the Sum of the numbers written in the Amount_hours column.
To elaborate:
I can easily sum the amount of hours spent by a filtering rule:
Sum(
Filter(
[datasource],
Registered_By = ["ValueOfRegistered_By"]
),
Amount_Hours
)
Now I only need to accomplish the same, just for the dates between 26th of the previous month untill the 25th of this month.
Update:
OK I think I managed to find a way to get what I want, it also allows me to have more control to select the date ranges I want to report on.
- I added 2 Date Pickers (Labeled as "DateFrom and DateTo respectively) as selector for From and To date
- The "From:" Date Picker contains the following code in the Default Property:
DateAdd(
DateAdd(
Date(
Year(Today()),
Month(Today()),
25
),
-1,
Months
),
1,
Days
)
Which essentially sets the date to 26th of the previous month.
2. The "To:" Date Picker contains a simple formula to display the 25th of the current month:
Date(
Year(Today()),
Month(Today()),
25
)
Which looks a little like this:
- Then I set a few labels (like a small table) to display what I want to see in terms of Total amount and the Filtered amount.
Which looks like this:
- Total Column: contains the formula contains the formular earlier in the post to sum the amount of hours for each row in the excel column.
- The Filtered amount contains essentially the same formula as the total amount, but only contains 2 additional logical tests to allow filtering by the datepickers I added above.
Text(
Sum(
Filter(
Timesheet_1,
Registered_By = "Kenny",
Date > DateFrom.SelectedDate,
Date < DateTo.SelectedDate
),
Amount_Hours
)
)
Thats it. I tested with a few entries and it works for me as expected.
Credit also goes to the following posts which I have found later on after you have provided me with a few pointers:
Help with "sumif" and days between dates
and Set Default date of Date Picker to first and last day of that Month
Thank you very much for your help, the problem is solved for me now 🙂
Regards,
Kenny
