Not Showing the Total Sales of Last Week (Last Month)

[ADSL]

Hi BI Community Team,

I have table of sales order, customer & supplier and looking for weekly sales comparion between current & last month. After applied the measure, grand total of last week (last month) doesn't show as the screenshot below.

Any suggestion & advise of this issue?

Thanks and Regards,

[ADSL]

Hi All,

Thank you for your helpful feedback and advise.

Best Regards,

[Anonymous]

Hi @ADSL 

Thank you very much rajendraongole1 and lbendlin   for your prompt reply.

I made some modifications to your code, as follows:

Last Week Sales (PM) = 
VAR _week = SELECTEDVALUE('Calendar'[Week of Month])
VAR _month = SELECTEDVALUE('Calendar'[Date].[MonthNo])
VAR _year = SELECTEDVALUE('Calendar'[Date].[Year])
VAR _isTotal = ISINSCOPE('Calendar'[Week of Month]) = FALSE()
RETURN
IF(
    _isTotal,
    CALCULATE(
        SUM('Sales Order'[Amt]), 
        FILTER(
            ALL('Calendar'), 
            YEAR('Calendar'[Date]) = _year 
            && 
            MONTH('Calendar'[Date]) = _month-1
        )
    ),
    CALCULATE(
        SUM('Sales Order'[Amt]), 
        FILTER(
            ALL('Calendar'), 
            YEAR('Calendar'[Date]) = _year 
            && 
            'Calendar'[Week of Month] = _week 
            && 
            MONTH('Calendar'[Date]) = _month-1
        )
    )
)
 

Here is the result.

Regards,

Nono Chen

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

[rajendraongole1]

Hi @ADSL - you can try the below suggested solution from @lbendlin 

adding another workaround, Your measure for "Last Week" likely calculates values per week, but at the total level, it might not aggregate correctly. 

LastWeekSales =
CALCULATE(
SUM(Sales[SalesAmount]),
DATEADD(Sales[Date], -7, DAY)
)

To ensure correct totals, use ISINSCOPE to distinguish between row-level and total-level calculations.

LastWeekSales =
IF(
ISINSCOPE(Sales[Customer]),
CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -7, DAY)),
SUMX(
VALUES(Sales[Week]),
CALCULATE(SUM(Sales[SalesAmount]), DATEADD(Sales[Date], -7, DAY))
)
)

There should be a Date table marked as a date table in Power BI.Relationships between your sales table and the date table should be established correctly.

Hope this works . try both solutions . Thank you.

[lbendlin · Accepted solution]

looking for weekly sales comparion between current & last month

weeks and months are incompatible.

Best you can do is date math like so

Last Week Sales (PM) = CALCULATE([MTD Sales],DATEADD('Calendar'[Date],-7,DAY))

(whatever MTD means to you - you should use the Calendar Date for this.)