Forum Discussion

psmith-nhs-inc's avatar
psmith-nhs-inc
Helper 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])
					)
				)
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi psmith-nhs-inc

 

Sounds like you are just after a cumulative measure similar to this :

 

Cumulative Incoming = CALCULATE(
				SUM(Incoming[Incoming Qty]) ,
				FILTER(
					ALL('Incoming'),
					'Incoming'[Incoming Date]<=MAX('Dates'[Date])
					)
				)

I have a PBIX file you can try it with here https://1drv.ms/u/s!AtDlC2rep7a-kHTghFw8Upt_GDbN

psmith-nhs-inc's avatar
psmith-nhs-inc
Helper 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
    Microsoft 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
      Helper III
      9 years ago

      That seems to work perfectly.  Thank you very much!