Can't get subtotal to work with formula on fetched data

[Jacob_v_Jensen]

I have an table with data of sales orders, that get fetched every day. For this example, relevant is that i have an order number, a fetched date and a ordervalueamount for that giving day. 

I have tried something in the lines of:

VAR MaxFetchedDate = MAX('NavCurrentOrders (Fra SQL database)'[FetchedDate])

RETURN

    CALCULATE(

        SUM('NavCurrentOrders (Fra SQL database)'[OrderValueAmount]),

        'NavCurrentOrders (Fra SQL database)'[FetchedDate] = MaxFetchedDate

    )

This is the result for a giving order number in a matrix, with the fethced dates as months, where the formula doesn't work:

Any ideas to what to do?

[Ashish_Mathur · Accepted solution]

Hi,

This measure pattern should work

Measure = SUMX(VALUES(Calendar[Month name]),[your measure])

[Jacob_v_Jensen]

Thanks for the reply

This works for the use case above, thanks!

[Ashish_Mathur]

You are welcome.

[v-zhengdxu-msft]

Hi @Jacob_v_Jensen 

Thanks for the reply from @DataNinja777 , please allow me to provide another insight:

You're overthinking this question, just change measure to:

SUM('NavCurrentOrders (Fra SQL database)'[OrderValueAmount])

The subtotal could be working fine.

Best Regards

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

[Jacob_v_Jensen]

Thanks for the reply, although this wouldn't work, as it wouldn't just sum the latest fetched data/row. A quick example of my dataset is such: 

so for april or just the latest date for that sake, it should only return 200 and 340 for the two order numbers. Just summing, would add all the values together giving 600 and 1020 in this example

[DataNinja777]

Hi @Jacob_v_Jensen 

What about something like below?

VAR MaxFetchedDate =
    MAX ( 'NavCurrentOrders (Fra SQL database)'[FetchedDate] )
RETURN
    CALCULATE (
        SUM ( 'NavCurrentOrders (Fra SQL database)'[OrderValueAmount] ),
        KEEPFILTERS ( 'NavCurrentOrders (Fra SQL database)'[FetchedDate] = MaxFetchedDate )
    )

 Best regards,

[Jacob_v_Jensen]

Thanks for the reply, with this the subtotal still doesn't work