Calculate: 2 different Dates with different Criteria and Relationships.

[EduardoSolaft]

Hi,

My calculation is very easy to do in Excel, but it's been hard to do it in DAX.

I need to calculate on each Month End the Orders that haven't been invoiced yet, to support my Backlog calculation.

In my Invoice Fact Table I have "Invoice Date" (active relationship with Calendar Table) and "Order Date" (inactive relationshp with calendar).

In Excel I can work it out with a simple SUMIFS using the following criteria:

Invoice Date > End of Month (Invoice Date must be in the future, to make sure it haven't been invoiced yet).

Order Date <= End of Month (Order Date must be in the past, to make sure it exists).

The image below shows an example of how it works:

I need to create a measure using CALCULATE, identifying the LAST DAY of the Date Context, with 2 filters:

Filter 1: Invoice Date > Last Day of filter context (where Invoice Date has active relationship with Calendar Table),

Filter 2: Order Date <= Last Day of filter context (where Order Date has inactive relationship with Calendar Table).

I'd appreciate the help of the community.

Kind Regards,

Eduardo Sousa.

[v-cgao-msft · Accepted solution]

Hi @EduardoSolaft ,

Please try this measure:

Backlog = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Invoice Date] > MAX ( 'Calendar'[End of Month] )
            && 'Table'[Order Date] <= MAX ( 'Calendar'[End of Month] )
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[EduardoSolaft]

Hi,

It works well!

I've just changed the ALL to ALLSELECTED, because I still need to use a Slicer.

Thank you!