Forum Discussion

khisla's avatar
khisla
Icon for Helper II rankHelper II
1 year ago
Solved

Cumulative sum with exclusions

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 th...
  • MasonMA's avatar
    MasonMA
    1 year ago

    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!