SUM with date filters

[PBIUWO]

Hi,

I have 2 excel files.

The first one shows the list of quantity demanded for each item by month.

The second one shows the last possible sale date for each item. (There can be no quantity demanded after the specified last sales date. )

How can I create a calculation to show how much quantity for each item is being demanded after the specified last sales date?

(I'm having troubles because the last sale date is unique to each item) 

Thank you

[v-yuta-msft]

@PBIUWO,

Could you please share some sample data for further analysis?

Regards,

Jimmy Tao

[PBIUWO]

Table 1 has date item number, quantity demanded per month. Table  2 has the Item and the Last Sale Date.

I want to create a CALCULATION, where I can see the quantity demanded that is past the last sale date per item.  

[Ashish_Mathur · Accepted solution]

Hi,

Build a relationship from the Item column of Table1 to the Item column of Table2.  In your visual, drag the Item column from Table2.  Write these measures

Last date = MAX(Table2[Last sale date])

Quantity sold beyond the last sale date = if(HASONEVALUE(Table2[Item]),CALCULATE(SUM(Table1[Quantity]),FILTER(VALUES(Table1[Plan]),Table1[Plan]>[Last date])),SUMX(SUMMARIZE(VALUES(Table2[Item]),[Item],"ABCD",CALCULATE(SUM(Table1[Quantity]),FILTER(VALUES(Table1[Plan]),Table1[Plan]>[Last date]))),[ABCD]))

Hope this helps.

[PBIUWO]

Thank you for the reply! 

I tried this and have gotten the error, "Too many arguments were passed to the HASONEVALUE function. The maximum argument count for the funciton is 1". 

[Ashish_Mathur]

Hi,

Check your brackets

[Anonymous]

HI @PBIUWO

  Could you please post sample data with expected output? This will help us to help you.

Thanks

Raj