Forum Discussion

psmith-nhs-inc's avatar
psmith-nhs-inc
Icon for Helper III rankHelper III
9 years ago
Solved

Date math problem: if (ExpectedDate<=date, QTY, 0)

I have a standard Date table, Sales table, and Incoming table. The Sales Table and Incoming Table both have QTY, and a Sales_Date for sales, and an Expected_Date for Incoming.   I plot daily sales...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi psmith-nhs-inc

     

    Try this slight modification...

     

    Cumulative Incoming = CALCULATE(
				SUM(Incoming[Incoming Qty]) ,
				FILTER(
					ALLSELECTED('Incoming'),
					'Incoming'[Incoming Date]<=MAX('Dates'[Date])
					)
				)
psmith-nhs-inc's avatar
psmith-nhs-inc
Icon for Helper III rankHelper III
9 years ago

Awesome!  So I have this:

 

IncomingQTYCumulative = CALCULATE(SUM(IncomingInventories[order_qty]),FILTER(ALL(IncomingInventories),'IncomingInventories'[expected_date]<=MAX('Date'[Date])))

 

And while it works perfectly, these inventory numbers are by Item_ID, relating to 'item[Item_id] and this measure is immune to slicers tied to the Item table.  I know why, because of the ALL(IncomingInventories), but I need to be able to slice by 'Item[Stat_ID].

 

I can hardcode it into the measure, but that is not a good solution.  How can I allow the slicer to carry through?

 

Thanx

 

 

 

 

 

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi psmith-nhs-inc

 

Try this slight modification...

 

Cumulative Incoming = CALCULATE(
				SUM(Incoming[Incoming Qty]) ,
				FILTER(
					ALLSELECTED('Incoming'),
					'Incoming'[Incoming Date]<=MAX('Dates'[Date])
					)
				)
  • psmith-nhs-inc's avatar
    psmith-nhs-inc
    Icon for Helper III rankHelper III
    9 years ago

    That seems to work perfectly.  Thank you very much!