Cumulative sum with exclusions

khisla — Sun, 13 Jul 2025 11:55:23 GMT

Hello BI Community

I am looking to compare Daily Target to Actual Daily Net Sales.

I have a query with the Daily Targets and a seperate query with the Daily Sales Transactions.

I need to add up the Sales Transaction by store & by date and add the column to Daily Target query.

Additionally, I need to only include sales transactions with - (1) Transaction Type = Sales and (2) Entry Status = Blank

Query 1

Store	Date	Target
IL204	01/04/2025	28,000
IL204	02/04/2025	25,000
IL204	04/04/2025	33,000
IL207	13/04/2025	38,000
IL207	14/04/2025	35,000
IL207	15/04/2025	-

Query 2

Receipt No.	Transaction Type	Entry Status	Store No.	Date	Net Amount (excl. VAT)
000IL20401000219880	Float Entry		IL204	01/04/2025	-
000IL20401000219881	Sales		IL204	01/04/2025	- 339
000IL20401000219882	Sales		IL204	01/04/2025	- 169
000IL20401000219883	Sales		IL204	01/04/2025	- 85
000IL20401000219884	Sales		IL204	01/04/2025	220
000IL20401000219885	Sales	Voided	IL204	01/04/2025	- 70
000IL20401000219926	Open Drawer		IL204	01/04/2025	- 50
000IL20401000220022	Tender Decl.		IL204	01/04/2025	- 200
000IL20401000220023	Float Entry		IL204	02/04/2025	- 70
000IL20401000220024	Sales		IL204	02/04/2025	- 156
000IL20401000220025	Sales		IL204	02/04/2025	- 152
000IL20401000220026	Sales		IL204	02/04/2025	- 25
000IL20401000220147	Tender Decl.		IL204	02/04/2025	- 50
000IL20401000220283	Float Entry		IL204	04/04/2025	-
000IL20401000220310	Sales	Voided	IL204	04/04/2025	- 200
000IL20401000220311	Sales		IL204	04/04/2025	- 85
000IL20401000220319	Open Drawer		IL204	04/04/2025	-
000IL20401000220318	Sales		IL204	04/04/2025	- 601
000IL20401000220323	Sales		IL204	04/04/2025	- 212
000IL20401000220375	Sales		IL204	04/04/2025	- 227
000IL20401000220376	Sales	Voided	IL204	04/04/2025	500
000IL20401000220377	Sales		IL204	04/04/2025	- 83
000IL20401000220378	Sales		IL204	04/04/2025	- 136
000IL20401000220379	Sales		IL204	04/04/2025	- 127
000IL20401000220380	Sales		IL204	04/04/2025	- 212
000IL20401000220381	Sales		IL204	04/04/2025	- 159
000IL20401000220382	Tender Decl.		IL204	04/04/2025	1,000
000IL20701000067072	Float Entry		IL207	14/04/2025	- 350
000IL20701000067073	Float Entry		IL207	14/04/2025	700
000IL20701000067076	Sales		IL207	14/04/2025	- 415
000IL20701000067077	Sales		IL207	14/04/2025	- 1,050
000IL20701000067080	Sales		IL207	14/04/2025	- 771
000IL20701000067081	Sales		IL207	14/04/2025	305
000IL20701000067082	Sales	Voided	IL207	14/04/2025	- 1,500
000IL20703000133373	Tender Decl.		IL207	15/04/2025	- 254
000IL20703000133374	Sales		IL207	15/04/2025	- 1,465
000IL20703000133375	Sales		IL207	15/04/2025	- 745
000IL20703000133376	Open Drawer		IL207	15/04/2025	- 500
000IL20703000133377	Float Entry		IL207	15/04/2025	- 288
000IL20703000133378	Sales	Voided	IL207	15/04/2025	- 2,000
000IL20703000133379	Sales		IL207	15/04/2025	- 136
000IL20703000133380	Sales		IL207	15/04/2025	763

The figures in BOLD should be included in the Actual Net Sales figure

Expected Result

Store	Date	Target	Actual Net Sales
IL204	01/04/2025	28,000	- 373
IL204	02/04/2025	25,000	- 334
IL204	04/04/2025	33,000	- 1,840
IL207	13/04/2025	38,000	-
IL207	14/04/2025	35,000	- 1,931
IL207	15/04/2025	-	- 1,584

Re: Cumulative sum with exclusions

FBergamaschi — Sun, 13 Jul 2025 14:47:20 GMT

My result

I created a key to connect the two tables with a relationship

key = Actual[Date] & Actual[Store No.]

key = Target[Date] & Target[Store]

Then created the below measure

Actual =

CALCULATE(

SUM ( Actual[ Net Amount (excl. VAT) ] ),

Actual[Entry Status] = "",

Actual[Transaction Type] = "Sales"

)

File here

https://drive.google.com/drive/folders/1UBj9nu519dGP3q1w0EM2Cs8vI_UTRzsM?usp=sharing

If this helped, please consider giving kudos and mark as a solution

mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Cumulative sum with exclusions

MasonMA — Sun, 13 Jul 2025 18:43:20 GMT

khisla

Hi, i'd suggest leveraging Power Query UI to get the work down with a few simple clicks;

1. Filter your Query2 according to your message 'I need to only include sales transactions with - (1) Transaction Type = Sales and (2) Entry Status = Blank'

2. On your Query2, Group by 'Store' and 'Date' to Sum the 'Net Amount'.

3. Merge the transformed Query1 and Query2 to a new Query based on 'Store' and 'Date'.

4. Expand your 'Actual Net Sales' to get your final table for Reporting.

No code option. Hope it helps!

Re: Cumulative sum with exclusions

FBergamaschi — Mon, 14 Jul 2025 08:48:30 GMT

khisla

Is mine or MasonMA answer a solution?

Please let us know

Re: Cumulative sum with exclusions

v-tejrama — Mon, 14 Jul 2025 12:22:23 GMT

Hi khisla

Thanks for reaching out to the Microsoft fabric community forum.

Has your issue been resolved?If the response provided by FBergamaschi and MasonMA addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Best Regards,
Tejaswi.
Community Support

Re: Cumulative sum with exclusions

khisla — Mon, 14 Jul 2025 12:29:32 GMT

@MasonMA was a solution

topic Re: Cumulative sum with exclusions in DAX Commands and Tips

Cumulative sum with exclusions

Re: Cumulative sum with exclusions

Re: Cumulative sum with exclusions

Re: Cumulative sum with exclusions

Re: Cumulative sum with exclusions

Re: Cumulative sum with exclusions