how to leverage the code below based on the expected answer, using lastnonblankvalue
,Hasonevalue
or something relevant rather then MAX()
? many thanks in adcance.
Current Week Amt =
VAR CurrentFiscalWeek = MAX( 'DF'[Fiscal Week num] )
RETURN
CALCULATE(
[Amt],
'DF'[Fiscal Week num] = CurrentFiscalWeek
)
Previous Week Amt =
VAR CurrentFiscalWeek = MAX( 'DF'[Fiscal Week num] )
VAR PreviousFiscalWeek = CurrentFiscalWeek - 1
RETURN
CALCULATE(
[Amt],
'DF'[Fiscal Week num] = PreviousFiscalWeek
)
Sample Dataset
Source Fiscal Week num Current Week Amt
Company A 52 5
Company A 51 10
Company A 50 20
Company B 50 1
Company B 48 2
Company B 47 3
What I am getting
Source Cur/Pre Week Amt
Company A 52 5
Company A 51 10
Company B 52 0
Company B 51 0
Expected Answer
Source Cur/Pre Week Amt
Company A 52 5
Company A 51 10
Company B 50 1
Company B 48 2
How about this?
Amt =
VAR i = VALUES('Table'[Source])
VAR j = VALUES('Table'[Fiscal Week num])
VAR t =
TOPN(2,
CALCULATETABLE('Table', REMOVEFILTERS(), i),
'Table'[Fiscal Week num], DESC)
RETURN
CALCULATE(SUM('Table'[Current Week Amt]),t , j)