Forum Discussion

jamuka's avatar
jamuka
Helper IV
3 years ago
Solved

Find Top - Bottom Performers

Hello all,   I'd like to show top - bottom brands, based on   margin % of last 4 weeks sales Margin % Last 28 Days Sales = CALCULATE ( [Total Margin %],     DATESINPERIOD ( 'Sales'[Sales D...
Need Help
Show and Tell
  • jamuka's avatar
    jamuka
    3 years ago

    I found the solution from another post calculating sum for max date. 

     

    Instead of using LASTDATE in my formula to find latest date in Sales Table I used MAX. 

     

    original code

    Margin % Last 28 Days Sales = 
CALCULATE (
    [Total Margin %],
    DATESINPERIOD ( 'Sales Report'[Sales Date], LASTDATE ( 'Sales Report'[Sales Date] ), -28, DAY )
)

     

    updated code

    Margin % Last 28 Days Sales_V2 = 
VAR LatestDate = 
CALCULATE(MAX('Sales Report'[Sales Date]), 
ALL('Sales Report'))
Return
CALCULATE (
    [Total Margin %],
    DATESINPERIOD ( 'Date'[Date], LatestDate, -28, DAY )
)

     

jamuka's avatar
jamuka
Helper IV
3 years ago

I found the solution from another post calculating sum for max date. 

 

Instead of using LASTDATE in my formula to find latest date in Sales Table I used MAX. 

 

original code

Margin % Last 28 Days Sales = 
CALCULATE (
    [Total Margin %],
    DATESINPERIOD ( 'Sales Report'[Sales Date], LASTDATE ( 'Sales Report'[Sales Date] ), -28, DAY )
)

 

updated code

Margin % Last 28 Days Sales_V2 = 
VAR LatestDate = 
CALCULATE(MAX('Sales Report'[Sales Date]), 
ALL('Sales Report'))
Return
CALCULATE (
    [Total Margin %],
    DATESINPERIOD ( 'Date'[Date], LatestDate, -28, DAY )
)