Grouping sales data for consective dates for repetative product ids

[vinaykumarnaka]

Greeting

As you can see in my data set the dates are repeating due to different unique product ids.

I want to group quantities for two consecutive days for similar product id

For example if  the product id   

ab1234    has  2   quantities on  06-07-2021   then in a separate column as a result quantities should be 2 which is (0+2 that is yesterdays quantity 0 + today's quantity  2)

ab1234    has  5   quantities on  07-07-2021   then quantities should be 7 which is (2+5 that is yesterdays quantity 2 + today's quantity  5))

ab 1234   has  4   quantities on  08-07-2021   then quantities should be 9 which is (5+4)

ab 1234   has  9   quantities on  09-07-2021   then quantities should be 13 which is (4+9).

looking forward to see some help

ab 1234   has  7   quantities on  10-07-2021   then quantities should be 2 which is (9+7)

[parry2k]

@vinaykumarnaka you should add a calendar table in your model, set a relationship with the transaction table on the date column, and add the following measure. You can follow my blog post here to add a calendar table. Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

Total = 
CALCULATE ( 
SUM ( YourTable[YourColumn] ),
DATESINPERIOD ( CalendarTable[Date], MAX( CalendarTable[Date] ), -1, DAY )
)

In visualization, use date from calendar table (not from transaction table)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[vinaykumarnaka]

Greetings, as you can see on the left-hand side 2 days sale is an output of your solution in the middle, is a manual calculation I have created as a check and the left-hand side is the actual sale quantity for that particular date.

usually, the manual calculation and measure I have created should match but it's not.

[V-lianl-msft · Accepted solution]

Hi @vinaykumarnaka ,

I can probably understand what you mean. Sort the date column first, and use this as the filter condition for calculation.

rank_date = RANKX('Table','Table'[Process_dt],,ASC,Dense)

last_period = 
var current_index = MAX('Table'[rank_date])
return CALCULATE(COUNT('Table'[Product_id]),FILTER(ALLEXCEPT('Table','Table'[Product_id]),'Table'[rank_date]=current_index-1))+0

2_date_count = COUNT('Table'[Product_id])+[last_period]

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vinaykumarnaka]

@amitchandak @parry2k @Fowmy @selimovd @v-rzhou-msft 

please can anyone help