Cumulative sum with exclusions

[khisla]

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

[khisla]

@MasonMA was a solution

[v-tejrama]

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

[MasonMA · Accepted solution]

@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! 

[FBergamaschi]

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

[FBergamaschi]

@khisla 

Is mine or @MasonMA answer a solution?

Please let us know