Need to Get the sum base on the date range in each Row

Ashanzulfick — Wed, 28 Feb 2024 04:39:41 GMT

Hello, I have a data set and need to calculate the sales quantity using the *Last GRN date* and *Last GRN to date* columns in the pivot table I've shared below. The date ranges differ depending on the item code in each row. Could someone please help me how to write a measure to get the correct sales quantity based on Column(1) and Column(2).

Re: Need to Get the sum base on the date range in each Row

ali_hijazi — Wed, 28 Feb 2024 11:14:28 GMT

so I suggest the following:
each row per item has a from and to date columns
we need to keep the rows that fall between the from and to in the GRN data for each Item
we can accomplish this using IntervalMatch

so the script would look something like this:

Sales:
load * from Sales.xlsx (this is a pseudo code)

GRN:
load Code as ITEM_CODE
, [Last GRN Date], [Last GRN to Date]

from GRN.xlsx

inner join IntervalMatch(DATE,ITEM_CODE)
load [Last GRN Date], [Last GRN to Date], ITEM_CODE
Resident GRN;
Drop Table GRN;
this way records not falling within the Last GRN Date and Last GRN to Date will be eliminated

note: the item code field name in both tables should be named the same to match the date with the related item's ranges of dates

topic Re: Need to Get the sum base on the date range in each Row in App Development

Need to Get the sum base on the date range in each Row

Re: Need to Get the sum base on the date range in each Row