How to get MAX Date that is filtered by ID and snapshot calendar?

[DoraRS]

Hi,

I'm having issue with DAX calculated column.
I have fact Sales History table (daily snapshots of sales) - relationship to Calendar table: CalendarID - Planned Delivery Date ID. 

There is no direct relationship to Calendar Snapshot table (to see snapshots of data as of chosen date) - Calendar Table is used to filter data by Planned Delivery Date, so we can see how sales orders were progressing over time.

Now, Sales History table has multiple Planned Delivery Date entries on the same day for one ID (modifications). 

I want to calculate in a column MAX date for order ID that will be also filtered by Calendar Snapshot.

Max PRD =

VAR _table  =   CROSSJOIN ( DISTINCT ( 'Calendar - Snapshot'[Calendar Date] ), DISTINCT ( 'Sales History'[OrderID] ) )

VAR _result =  CALCULATE( MAX ( 'Sales History'[Planned Receipt Date] )

,  ALLEXCEPT (  'Sales History', 'Sales History'[OrderID] ) )

RETURN

_result

It cannot be a measure as I need it for further analyses.

OrderID	Planned Receipt Date	Calendar Date (snapshot date)	Max PRD	What I'd like
12345	3/22/2024	3/1/2024	4/15/2024	3/22/2024
12345	3/22/2024	3/2/2024	4/15/2024	3/22/2024
12345	3/22/2024	3/3/2024	4/15/2024	3/22/2024
12345	3/22/2024	3/4/2024	4/15/2024	3/22/2024
12345	3/22/2024	3/5/2024	4/15/2024	4/2/2024
12345	4/2/2024	3/5/2024	4/15/2024	4/2/2024
12345	4/15/2024	3/6/2024	4/15/2024	4/15/2024

[v-jialongy-msft · Accepted solution]

Hi @DoraRS 

Please try the following DAX:

Max = 
CALCULATE(
    MAX('Table'[Planned Receipt Date]), FILTER('Table',
    'Table'[OrderID] = EARLIER('Table'[OrderID])&&
    'Table'[Calendar Date]<= EARLIER('Table'[Calendar Date])
)
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wilson_]

DoraRS,

If I am following your data and question correctly, you want the calculated column to be dynamically calculated, based on slicer selections? If so, this is impossible because calculated columns are calculated at model refresh time.

Not sure why you need it as a calculated column, but unless you're wanting to use these values in your visual's row or column, I'm not convinced it couldn't be a measure.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or to DM me on here to discuss how we can make your problems disappear. 😄

[DoraRS]

Hmm...

I need to calculate sales order values that have been pushed to another month - and to be able to see when it happened (calendar snapshot)

so, case scenario:

We have total sales orders value of 50.000 with planned receipt date for April - and this is what we see as of March 5th. 

As of March 6th we have 40.000 - because 10.000 were moved to May.

So - my idea was to calculate Planned Receipt Date for Calendar Snapshot Date March 5th and compare it with Planned Receipt Date for Calendar Snapshot Date March 6th. 

If Planned Receipt Date March 5th < Planned Receipt Date March 6 then it means this order has been postponed.

I created a measure... but I need to visualize it on Total Sales Order Value per each Calendar Snapshot Date - and this takes MAX Planned Receipt Date of all Order IDs as they are aggregated, and this still needs to be treated on a row level... The only idea I had was to have a calculated column which would correspond to specific OrderID visible on certain Calendar Snapshot Date.

Not sure if I explained it correctly...