Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
psmith-nhs-inc
Helper III
Helper III

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 on a line chart, with a measure that simply sums Sales_QTY (Sales_QTY_SUM).  I don't have to do anything else, if Date is on the axis, I get what I want, for sales.  I also have a measure that sums Incoming_QTY (Incoming_QTY_SUM), and it works the same way, but that is not exactly what I want.

 

I want to see Incoming_QTY as a cumulative total going forward from Expected_Date.  So if an incoming record has a Date of March 1st, I want to see 0 before March 1st, and Incoming_QTY in the line chart results starting on March 1st, then on March 2nd, and March 3rd, ..

 

Date[Date] and Incoming[Expected_Date) are both columns, and trying to use them in an IF statement fails because I am not using any aggregates.

 

IF([Expected_Date]<=[Date],Incoming_QTY,0) is the logic.

 

How do end up with a measure that will plot this properly on a line chart?

Thanx

 

Phil

1 ACCEPTED SOLUTION

Hi @psmith-nhs-inc

 

Try this slight modification...

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

 

 

 

 

Hi @psmith-nhs-inc

 

Try this slight modification...

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

That seems to work perfectly.  Thank you very much!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.