Sum positive values per day and aggregate per week

[Munckin]

Hello,

I am quite new to PowerBI and this is my first time posting here.

What I am trying to accomplish is to retrieve the sum of all sales that are above a certain day threshold and aggregate these values per week. 

I have the following example data: 

Sales

Date	Time	Net sales
1-5-2023	13:00	100
1-5-2023	14:00	50
1-5-2023	15:00	70
2-5-2023	12:00	150
2-5-2023	16:00	30
3-5-2023	10:00	200
4-5-2023	11:00	160
4-5-2023	11:00	10
5-5-2023	13:00	80
5-5-2023	14:00	150
5-5-2023	15:00	20
6-5-2023	15:00	170
7-5-2023	12:00	190
7-5-2023	16:00	40

Threshold

Date	Threshold
1-5-2023	150
2-5-2023	190
3-5-2023	250
4-5-2023	180
5-5-2023	150
6-5-2023	200
7-5-2023	200

Above Threshold =

VAR Surplus =

   CALCULATE(SUM('Sales[Net sales]) - Threshold[Threshold])

RETURN

    IF(Surplus > 0, Surplus , BLANK()))

When I create a matrix visual on day aggregation everything works fine. But when I try to use weeks, the negative values are also included when the total of a week is above the threshold total of a week. 

I am very curious to learn how I can solve this problem.

Thank you in advance! 

[andhiii079845 · Accepted solution]

You need a dimensional table for the date to bring both tables together in a model:

Here is the measure, you have to calculated the difference within the dates 

Measure =

VAR _sum = SUMMARIZE(DimDate,DimDate[Date],"_diff",if(sum(sales[Net sales])-sum(threshold[Threshold])>0,sum(sales[Net sales])-sum(threshold[Threshold]),BLANK()))

RETURN sumx(_sum,[_diff])

[Munckin]

Wow, you are fast! Your solution works like a charm. I am not yet familiar with the summarize function of DAX, so i'm gonna try and figure out exactly how the formula works. Thank you very much! 🙂 

[andhiii079845]

Enjoy your journey in DAX 🙂 

[andhiii079845]

You calculated the sum in your var and than you check if the sum is greater as 0. 

If you want to check the sum per day row per row, you have to use sumx().