Card with value of last week with an amount

[Anonymous]

Hi all,

I have a date table and a transaction table.

The date table has more dates than the transaction table.
I want to create a card showing the value of the last with week a value.
Somehow my formula doesn't work though....

It's probably simple, but I just can't see how I fix this.

Who can help me please?

My formula:

amount of max week =

VAR lastweekwithamount = CALCULATE(MAX('Date'[Week]),ALLSELECTED('Transaction'[Amount]))

RETURN

CALCULATE(SUM('Transaction'[Amount]), 'Date'[Week] = lastweekwithamount)

It shows a blank in my card. It's probably because there are only transactions in week 1 and 2, not in the later weeks....

If I create a table with the week and the amount, it only shows week 1 and 2.
I want to see the value of week 2 as a result of my formula - to present in a card - , but this doesn't happen. Why?

[Anonymous · Accepted solution]

I found the solution I needed myself in the meanwhile:

amount of max week =

VAR AmountsGreaterThanZero = FILTER('Transaction','Transaction'[Amount]>0)

VAR LastWeekWithAmountGreaterThanZero = CALCULATE(MAX('Date'[Week]),AmountsGreaterThanZero)

RETURN

CALCULATE(

SUM('Transaction'[Amount]),

FILTER('Date','Date'[Week]=LastWeekWithAmountGreaterThanZero),

AmountsGreaterThanZero

)

[ryan_mayu]

@Anonymous 

maybe you can try

amount of max week =
VAR lastweekwithamount = year(MAX('Transaction'[Date]))&month((MAX('Transaction'[Date]))
RETURN
CALCULATE(SUM('Transaction'[Amount]), 'Date'[Week] = lastweekwithamount)