Dynamic filter for totals calculation

Anything related to PowerPivot and DAX Formuale
jns
Posts: 1
Joined: Fri Nov 20, 2020 8:41 pm

Dynamic filter for totals calculation

Postby jns » Fri Nov 20, 2020 9:12 pm

Hi,

I would like to calculate the following:

I have a dataset with 3 columns
Customer | Day | OrderAmount

Now I want to select all the Customers (identified by ID) that have ordered something in the last 7 days and take that list of Customers to calculate their total OrderAmount over the entire Dataset, basically ignoring Customers that have not ordered in the last 7 days.

Running Sum 7 Days=
var max_date = max(Tabelle1[Day])
return
CALCULATE(sum(Tabelle1[OrderAmount]);DATESINPERIOD(Tabelle1[Day];max_date;-7;DAY))

Distinctcount Customers 7 Days=
var max_date = max(Tabelle1[Day])
return
CALCULATE(DISTINCTCOUNT(Tabelle1[Customer]);DATESINPERIOD(Tabelle1[Day];max_date;-7;DAY))

Image

In this example for the 01.11.2020 I would like to see the total of the 4 distinct customers that have been active in the last 7 days.
For the 20th Nov. there were 8 distinct customers actively purchasing in the last 7 days and I would like to see the overall total for these 8 customers.

I think the solution is to use the IN-operator correctly in DAX but I was not able to get it to work.

Thank you for your help in advance.

Cheers,
Jonas

MattAllington
Posts: 1144
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Dynamic filter for totals calculation

Postby MattAllington » Sun Nov 22, 2020 6:02 am

Looks like it is working to me. What is the problem?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests