topic Re: Adding numbers for a date plus all numbers for the previous 6 days in DAX Commands and Tips

Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Tue, 11 Jun 2024 22:08:52 GMT

I am having trouble creating a measure that adds the number in the Actual column for a particular day plus all of the Actual numbers for the previous 6 days.

Subcategory	Date	Actual	Sum (Actual + Actual for Previous 6 days)
Operator Maintenance (PM02)	6/1/2024	10
Preventative Maintenance (PM02)	6/1/2024	5
Corrective Maintenance (PM01)	6/1/2024	3
Operator Maintenance (PM02)	6/2/2024	44
Unplanned Maintenance (PM03)	6/2/2024	22
Corrective Maintenance (PM01)	6/2/2024	14
Operator Maintenance (PM02)	6/3/2024	2
Preventative Maintenance (PM02)	6/3/2024	20
Unplanned Maintenance (PM03)	6/3/2024	15
Corrective Maintenance (PM01)	6/3/2024	8
Preventative Maintenance (PM02)	6/4/2024	39
Unplanned Maintenance (PM03)	6/4/2024	12
Corrective Maintenance (PM01)	6/4/2024	2
Preventative Maintenance (PM02)	6/3/2024	16
Unplanned Maintenance (PM03)	6/3/2024	20
Corrective Maintenance (PM01)	6/3/2024	19
Corrective Maintenance (PM01)	6/4/2024	10
Operator Maintenance (PM02)	6/4/2024	4
Preventative Maintenance (PM02)	6/4/2024	3
Unplanned Maintenance (PM03)	6/4/2024	8
Corrective Maintenance (PM01)	6/5/2024	47
Operator Maintenance (PM02)	6/5/2024	19
Preventative Maintenance (PM02)	6/5/2024	2
Unplanned Maintenance (PM03)	6/5/2024	8
Corrective Maintenance (PM01)	6/5/2024	35
Operator Maintenance (PM02)	6/6/2024	15
Preventative Maintenance (PM02)	6/6/2024	16
Unplanned Maintenance (PM03)	6/6/2024	35
Corrective Maintenance (PM01)	6/6/2024	7
Operator Maintenance (PM02)	6/7/2024	45
Preventative Maintenance (PM02)	6/7/2024	2
Unplanned Maintenance (PM03)	6/7/2024	9
Corrective Maintenance (PM01)	6/7/2024	8
Preventative Maintenance (PM02)	6/8/2024	6
Unplanned Maintenance (PM03)	6/8/2024	13
Corrective Maintenance (PM01)	6/8/2024	8
Operator Maintenance (PM02)	6/9/2024	39
Preventative Maintenance (PM02)	6/9/2024	4
Unplanned Maintenance (PM03)	6/9/2024	13
Corrective Maintenance (PM01)	6/9/2024	5
Corrective Maintenance (PM01)	6/10/2024	12
Operator Maintenance (PM02)	6/10/2024	58
Preventative Maintenance (PM02)	6/10/2024	26
Unplanned Maintenance (PM03)	6/10/2024	2

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Wed, 12 Jun 2024 13:22:57 GMT

The last six calendar days or the last six days with data? What should happen for days without data?

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Wed, 12 Jun 2024 13:40:21 GMT

Hi,

The last 6 calendar days.

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Wed, 12 Jun 2024 14:29:29 GMT

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Thu, 13 Jun 2024 12:38:42 GMT

Thank you. The Seven days measure works perfectly. % Emergency is a measure that I created with the use of "seven days" as a denominator. When I delete "Date" from the report to summarize the % Emergency by month, I get an Infinity error as seven days becomes 0 in the report. How to create a separate seven days measure that will work for my monthly figures?

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Thu, 13 Jun 2024 13:17:52 GMT

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Weeks and months are incompatible.

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Thu, 13 Jun 2024 13:29:17 GMT

Is there a measure I can use that has the months and days logic? OR

Is there a separate months measure that I can use? I appreciate your help.

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Thu, 13 Jun 2024 13:40:41 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Thu, 13 Jun 2024 14:19:34 GMT

Here is my sample data:

Date	Actual	seven days
5/10/2024	9	63
5/11/2024	13	153
5/12/2024	13	217
5/13/2024	2	282

Here is the by date outcome which is working perfectly.

Here is what I need by month:

The seven days figure isn't calculating by month as the seven days measure doesn't calculate for the month.

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Thu, 13 Jun 2024 14:30:09 GMT

What is your expected outcome?

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Thu, 13 Jun 2024 14:36:08 GMT

Now, I see how this would be weird by month. I'll need to get clarification from the requester. This may take a few days. Thank you SO much for your help.

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Mon, 17 Jun 2024 17:54:51 GMT

Hi,

The expected result for the month of May is a total sum of the seven days figures for all of May. See data:

Date	Actual	seven days		% Emergency
5/1/2024		127
5/2/2024		105
5/3/2024		105
5/4/2024		105
5/5/2024		105
5/6/2024		105
5/7/2024	14	14
5/8/2024	43	57		11%
5/9/2024	16	73		10%
5/10/2024	63	136		7%
5/11/2024	90	226		6%
5/12/2024	64	290		4%
5/13/2024	84	374		1%
5/14/2024	16	376		1%
5/15/2024		333
5/16/2024		317
5/17/2024		254
5/18/2024		164
5/19/2024		100
5/20/2024	26	42		0%
5/21/2024		26
5/22/2024	8	34		21%
5/23/2024		34
5/24/2024		34
5/25/2024		34
5/26/2024		34
5/27/2024	31	39		0%
5/28/2024		39
5/29/2024	16	47		6%
5/30/2024		47
5/31/2024		47
Total		3823	<----------------	-----------------	Expected Result

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Mon, 17 Jun 2024 19:00:13 GMT

You are including the last 6 days in April?

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Mon, 17 Jun 2024 19:05:17 GMT

No. Would want to include only the figues for May.

Re: Adding numbers for a date plus all numbers for the previous 6 days

lbendlin — Mon, 17 Jun 2024 19:33:49 GMT

That's not what the data says

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Tue, 18 Jun 2024 06:02:54 GMT

Hi Anonymous

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Tue, 18 Jun 2024 14:51:37 GMT

I understand the monthly total of seven days doesn't align with the logic of the seven days measure. The requestor is aware of that.

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Fri, 21 Jun 2024 14:50:26 GMT

I am still looking for help with the last part of the ask.

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Fri, 21 Jun 2024 14:52:24 GMT

Hi,

Are you still able to help with the total sum for seven days for a month? I understand that it doesn't follow the logic previously dicussed. I just need a measure that sums all of the seven days figures for May.<please>

Re: Adding numbers for a date plus all numbers for the previous 6 days

Anonymous — Mon, 24 Jun 2024 12:30:16 GMT

Are you still willing to help with this?