Search code examples
powerbidaxpowerbi-desktopmeasuredaxstudio

the latest current/previous week available in DAX

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
Solution

  • How about this?

    enter image description here

    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)