topic Re: Sum if date is within last 7 calendar days of month in DAX Commands and Tips

Sum if date is within last 7 calendar days of month

vickikrebs — Wed, 09 Aug 2023 17:21:39 GMT

Hi,

I need to calculate the sum of the # of invoices, but only if the Date is in the last 7 calendar days of each month. I will have YTD data, updated daily.

# invoices	Date
7	6/27/2023
4	6/28/2023
2	6/29/2023
1	7/21/2023
2	7/22/2023
4	7/27/2023
5	7/28/2023
2	7/29/2023
3	7/31/2023

I thought about trying to create a column with "true" if it is in the last 7 days, and "false" if not. But I don't know how to write that. Anyone have ideas?

Re: Sum if date is within last 7 calendar days of month

AnthonyGenovese — Wed, 09 Aug 2023 18:35:41 GMT

I would try something like this

7daySum = Calculate(sum(NumberOfInvoices), datediff(invoicesDate,today(),DAY)<=7)

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: Sum if date is within last 7 calendar days of month

vickikrebs — Wed, 09 Aug 2023 19:02:36 GMT

I think..this one gives me past 7 days, instead of the last 7 days of the calendar month. Is it possible to get the last 7 days of the calendar month?

Re: Sum if date is within last 7 calendar days of month

AnthonyGenovese — Wed, 09 Aug 2023 19:11:08 GMT

How about...

7dayEOMSum =
var selectedDate=selectedvalue(invoicesDate)
Calculate(sum(NumberOfInvoices), datediff(selectedDate, EOM(selectedDate,0),DAY)<=7)

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: Sum if date is within last 7 calendar days of month

vickikrebs — Wed, 09 Aug 2023 19:15:28 GMT

Ooooh - that made me think. I got it to work with:

Last week =

VAR EOM = EOMONTH('Num Invoices'[Date], 0)

Var DateDiff = DATEDIFF('Num Invoices'[Date], EOM, Day)

RETURN IF(DateDiff >= 0 && DateDiff < 7, "True", "False")

Thank you!!!