Having a DAX issue with calculating correct total

[Erwin]

Hi all,

One of my clients has given me a table with their spend data. This table holds spend purchasing document data and the purchase orders they have been booked on. Below you will find a sample of this data

Sample order data

For the client it is very important to know the SPEND VALUE for the orders that were recorded AFTER the FIRST document was recorded. Furthermore, they want to limit this value to the orders that were created in the context year.

As you can see in the above table I've already added a column showing which order were created before the first document was created (TRUE) and which were created after the first document was created (FALSE).

Below are the steps I've already taken:

1. Created a measure for total spend (BTW, I've added a calendar table called Calendar).
   

   Total Spend = 
   CALCULATE (
       SUM ( 'Spend'[Amount] ),
       USERELATIONSHIP ( 'Spend'[Document date], 'Calendar'[Date] )
   )

   Below you will find the output.
   
2. Created a measure filtering out the orders that were created after the first document was created.
   

   Filter #1 = 
   CALCULATE (
       [Total Spend],
       FILTER ( Spend, Spend[Order before document] = FALSE () )
   )

   Below you will find the output.
   
3. Created a measure limiting the filtered amount to the orders created in the context year.
   

   Filter #2 = 
   CALCULATE (
       [Filter #1],
       DATESBETWEEN (
           Spend[Order date],
           MIN ( 'Calendar'[Date] ),
           MAX ( 'Calendar'[Date] )
       )
   )

   Below you will find the output.
   

As you can see the last measure "Filter #2" does not give the correct total. This is because we have a double filter on dates. First on document date (Total Spend measure) and later on on order date (Filter #2). On the total line both of these contexts are dropped, creating an incorrect total.

My question is obvious. What DAX code will calculate the correct total value? I'm thinking about using ISFILTERED, but still can't come up with any working code.

Can you help me?

Regards,

Erwin

[Greg_Deckler]

You have a measure total problem. See this article. https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[Erwin · Accepted solution]

Hi Greg,

I already knew I was having this particular problem. Nontheless, you've put me on the right track and I've come up with the code myself (which is always best, I think).

Filter #3 = 
IF (
    HASONEFILTER('Calendar'[Year]),
    [Filter #2],
    CALCULATE (
        [Filter #2],
        FILTER ( Spend, YEAR ( Spend[Order date] ) = YEAR ( Spend[Document date] ) )
    )
)

 

Many thanks for your advice.

Rg. Erwin