Problem with Understanding Order in Progress

HarsimranjeetSingh · June 4, 2020, 12:02pm

Greetings!! EDNA community

I’m using below code from course Solving Analytical Scenarios w/Power BI & DAX Chapter Sales and Orders in progress and couldn’t understand how it is returning rows.

For example this below expression in image 1 its showing 21 rows for date 2/6/14 where if we filter data on order date to this date it show 13 rows only and there are lots of order where shipping date is greater than this date. I believe Max and Min date in expression is the date of row context which is this situation for second row is 2/6/14.

Please help to understand this in details.

Also would like to suggest that please continue explaining in same how formula is working as in Master DAX lecture, this will help is someone coming to course again after gap but can recall with your guidance

DAX Code

Order Progress in Sales =

CALCULATE(COUNTROWS(Sales_Data),

FILTER(VALUES(Sales_Data[OrderDate]),Sales_Data[OrderDate]<=MAX(‘Date’[Date])),

FILTER(VALUES(Sales_Data[Ship Date]),Sales_Data[Ship Date]>=MIN(‘Date’[Date]))

)

Image1

Image2

Thanks,
HarryMastering DAX Calculations Data.pbix (454.3 KB)

Rajesh · June 4, 2020, 12:43pm

@HarsimranjeetSingh

You are using equal(=) operator in Power Query
Use OrderDate <= 02-06-2014
and then check Ship Date >= 02-06-2014

If my understanding is wrong, please share the PBIX file.

Melissa · June 4, 2020, 1:56pm

Hi @HarsimranjeetSingh,

The goal is to count the number of rows in Sales where the ‘Date’[Date] is equal to or between Order- and Shipdate. Now to make any sense of the screen dumps it’s important to see at least the Model and the date dimension used in the Visual.

Since it’s an eDNA dataset, I would suggest sharing the PBIX to get a quick and accurate response.