FIRST DATE (order date) for each customer, Ignoring Month Filter Context

[Anonymous]

Hi Community,

I am looking for some help on how to ignore a month when I add it to my table.

The intent of this table is to show if the customer did not order during baseline dates (example 8/1/22 thru 7/31/23), but has now ordered more than a qty of 2 after baseline dates then  SUM sales.  If they did order during baseline dates, then do not count sales.

Here are my dax formulas:

New Distribution Qty 

New Distribution Qty = IF([Total Qty Baseline]=0 && [Total Qty New Distribution] > 2, [Total Qty New Distribution],0)

Gaps Closed:

Gaps Closed = SUMX(SUMMARIZE('VALUE ENTRY', CUSTOMER[Territory Code],CUSTOMER[Customer Code],'ITEM'[Item No]), IF([Total Qty Baseline] =0 && [Total Qty New Distribution] > 2 , 1 ,0)+0)

Here is what the correct table would show:

But then when I go to add in the month column - the current dax formula will include each month of orders if baseline quantity was 0.  I am looking for the formula to count only the first sale order and ignore all of the future orders.

Here is what the table shows:

I will know the formula is working when Customer A shows only the sales order in August. 

September and October's sales orders would show as zero, since it was not the very first sale.

Really hoping someone can help me here

Thanks so much,

Christina

[scee07]

Hi, 

this is my guess how to do this. I will do a minimal toy example to explain my approach. Perhaps it helps. 
If you abstract from the specific problem you have a table visual and you have to replace the filter context for the date to calculate the same type of value but with a different filter context. In my example I will just do a business rule that says look at the measure ninety days earlier and if this is greater than zero than return 0 otherwise the value of the measure. The naive measure is the simplest you can imagine:

TestMeasureNaive =
SUM ( FactOnlineSales[SalesQuantity] )
MonthID = 
FORMAT(DimDate[Datekey], "yyyy-MM")

The table is from the contoso model, just to give me some data.  The Date Table gets a proper sortable month string. 
So, our stupid measure picks up the customer key and month filter context:

This is not what we want. Let's define an alternative measure that incorporates some business rule when to take this simple value or something else. 

TestMeasure =
VAR minDateInPeriod =
    MIN ( DimDate[Datekey] )
VAR referenceDate = minDateInPeriod - 30 // or whatever the lookback is
VAR monthString =
    CALCULATE (
        FIRSTNONBLANK ( DimDate[MonthID], 0 ),
        FILTER ( ALL ( DimDate ), DimDate[Datekey] = referenceDate )
    )
VAR referenceValue =
    CALCULATE (
        SUM ( FactOnlineSales[SalesQuantity] ),
        FILTER ( ALL ( DimDate ), DimDate[MonthID] = monthString )
    )
RETURN
    IF ( referenceValue > 0, 0, SUM ( FactOnlineSales[SalesQuantity] ) )

Now, the measure will still pick up the customer filter, but dependant on some arbitrary business rule it will take a different reference month, calculate the same type of value and if this value is greater than zero then the later month's value is set to zero. 

This obviously just a nonsense example to show the mechanism. A general pattern might be:

- define your naive measure with no filters applied in the measure
- calculate the change for all filters that are supposed , keep all filters that will not change

- calculate the value with the new filter context 

- apply whatever business rule taking into account the recalculated value

As I said, just a guess that this is the core of the problem. 

Best regards 

Christian