Forum Discussion

sammy02hk's avatar
sammy02hk
Icon for Helper I rankHelper I
3 years ago
Solved

Matrix table for Last last 4 week

I finally figure it out. Thank you all.

LLW Sales =

var LookupStart = LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
    SELECTEDVALUE(DateMatch[WkMonday]))
var Lookupend = (LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
    SELECTEDVALUE(DateMatch[WkMonday])))+6  
return
calculate(SUMX( 'Sales','Sales'[Sales]),
DATESINPERIOD(DateMatch[Date],LookupStart,7,DAY))



Hi there,

I would like to form a Matrix for last 4weeks.
I have upload the file:

1)

2) And I can drag different parameter into the visual.

 


I have tried using below syntax, but its not working.

LLW Sales =
var LookupStart = LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
    SELECTEDVALUE(DateMatch[WkMonday]))
var Lookupend = (LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
    SELECTEDVALUE(DateMatch[WkMonday])))+6  
return
calculate(SUMX( 'Sales','Sales'[Sales]),
 Sales[Billing_Date]>=LookupStart && Sales[Billing_Date]<=Lookupend)

Thank you for your help
Sammy Chu
  • sammy02hk's avatar
    sammy02hk
    3 years ago

    O... I finally figure it out. Thank you All.

    LLW Sales =
    var LookupStart = LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
        SELECTEDVALUE(DateMatch[WkMonday]))
    var Lookupend = (LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
        SELECTEDVALUE(DateMatch[WkMonday])))+6  
    return
    calculate(SUMX( 'Sales','Sales'[Sales]),
    DATESINPERIOD(DateMatch[Date],LookupStart,7,DAY))



2 Replies

  • sammy02hk's avatar
    sammy02hk
    Icon for Helper I rankHelper I
    3 years ago

    O... I finally figure it out. Thank you All.

    LLW Sales =
    var LookupStart = LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
        SELECTEDVALUE(DateMatch[WkMonday]))
    var Lookupend = (LOOKUPVALUE(DateMatch[LLW],DateMatch[Date],
        SELECTEDVALUE(DateMatch[WkMonday])))+6  
    return
    calculate(SUMX( 'Sales','Sales'[Sales]),
    DATESINPERIOD(DateMatch[Date],LookupStart,7,DAY))



    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi sammy02hk ,

       

      I am so glad that you could solve your issue by yourself. Please kindly Accept your workaround as the solution. More people will benefit from it. 

       

      Best Regards,
      Rico Zhou

       

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