topic Cumulative total by store and date in DAX Commands and Tips

Cumulative total by store and date

ekroll — Tue, 14 Jun 2022 20:21:45 GMT

I've been searching and can't seem to get this to work.. Fairly new to Power BI and DAX.

I have a table:

I need to create cumulative totals for the pallets by store and date.

I have tried:

Running_Total_Pallets_Built =
CALCULATE(
SUM('Pallets Built'[PALLETS]),
FILTER(
ALLEXCEPT('Pallets Built','Pallets Built'[STORENUMBER])
,'Pallets Built'[STAGEDDATE] <= MAX('Pallets Built'[STAGEDDATE])
)
)

but the result returns the total number all pallets built for that day for all stores:

My running total for store 12 should show:

Jan 4 - 2

Jan 5 - 4

Jan 6 - 5

Jan 7 - 6

Jan 8 - 7

There should be the same for the next store as well:

Store 13 should be:

Jan 4 - 2

Jan 5 - 4

Jan 6 - 5

Jan 7 - 6

And so on....

I've applied the following filters, which should be available when I publish as well:

Appreciate any help to get the last piece of the puzzle solved for this one.

Re: Cumulative total by store and date

ekroll — Tue, 14 Jun 2022 20:35:51 GMT

Well, I feel a fool... I started to look at the table visual again and realized that I was pulling in store number from a different table... they are connected via the store-date relationship, but it appears that once I removed the store number from the other (it's the capacity table) and dropped in the store number from the pallets built table, it now seems to be working as expected.

Re: Cumulative total by store and date

v-easonf-msft — Fri, 17 Jun 2022 09:33:29 GMT

Hi， ekroll

If your problem has been solved, please accept your reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason