cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## measure: total sum of negative sums for each customer

Hello everyone,

I'm trying to create a measure that calculates the sum of the revenue for each customer, where the total revenue of a specific time set is negative.

This is my dataset:

 invoice number invoice date start due date end due date customer revenue 500001 12.06.2021 12.06.2021 22.06.2021 Tom 100,00 € 500002 12.06.2021 12.06.2021 22.06.2021 Jack 50,00 € 500003 18.06.2021 18.06.2021 28.06.2021 Isabell -50,00 € 500004 19.06.2021 19.06.2021 29.06.2021 Tom -250,00 € 500005 27.06.2021 27.06.2021 07.07.2021 Harry 50,00 € 500006 01.07.2021 01.07.2021 11.07.2021 Jack 100,00 € 500007 01.07.2021 01.07.2021 11.07.2021 Frank -200,00 € 500008 01.07.2021 01.07.2021 11.07.2021 Tom 150,00 € 500009 04.07.2021 04.07.2021 14.07.2021 Isabell 50,00 €

Now, I would like to filter the dataset by a deadline, f.e. end of the due date is in the first two quarters:  end due date <= 30.06.2021

 invoice number invoice date start due date end due date customer revenue 500001 12.06.2021 12.06.2021 22.06.2021 Tom 100,00 € 500002 12.06.2021 12.06.2021 22.06.2021 Jack 50,00 € 500003 18.06.2021 18.06.2021 28.06.2021 Isabell -50,00 € 500004 19.06.2021 19.06.2021 29.06.2021 Tom -250,00 €

Total revenue per customer in this time period:

 customer revenue Tom -150,00 € Jack 50,00 € Isabell -50,00 €

Total revenue per customer in this time period that is negative:

 customer revenue Tom -150,00 € Isabell -50,00 €

Sum of the total revenue per customer in this time period that is negative:

(That would be the result I would like to see at the end)

It would be easy with group by in power query or with a calculated column, but then the end due date can't be changed by the users of the dashboard.

Thank you so much in advance!

1 ACCEPTED SOLUTION
Super User

@Louke , Assume date filter is already taken care in measure revenue

a new measure

sumx(Filter(values(Table[Customer]) , [Revenue]<0) , [Revenue])

sumx(values(Table[Customer]) , if( [Revenue]<0 , [Revenue], blank() ))

Super User

@Louke , Assume date filter is already taken care in measure revenue

a new measure

sumx(Filter(values(Table[Customer]) , [Revenue]<0) , [Revenue])

sumx(values(Table[Customer]) , if( [Revenue]<0 , [Revenue], blank() ))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.