SUM of all articles sold in current week, not latest week each item was sold

[calerof]

I have a file with back order per week for the last 10 weeks. I use it to create a history chart to show progress.

But for the current week, I need to create a chart per article with quantity and sales. My problem is that when I add the articles in the chart, my measure takes the sales of each item when it was last sold, i.e. if the last time article AA was sold was calendar week 16, then it includes that amount of quantity and revenue in the chart. I need to show only sales of current week.

My measures are the following:

Max CW = CALCULATE(
                    MAX(Backorders1[CW]),
                    ALLSELECTED(Backorders1)
                    )

Back Order Amount = CALCULATE(
                                      SUM(Backorders1[Precio BackOrder MXP]),
                                      FILTER(Backorders1, Backorders1[CW] = [Max CW])
                   )

Max CW returns correctly: "27" as an isolated indicator.

Back Order Amount returns correctly: "$205,450" as an isolated indicator.

Problem is when I include the articles, then happens the issue explained above, as follows:

 Could someone please help?

Thanks a lot.

Fernando

P.S. Enclose my data.

My Data

[OwenAuger · Accepted solution]

Hi Fernando,

You could use this variation on your current measure:

Back Order Amount =
VAR MaxCW = [Max CW]
RETURN
    CALCULATE (
        SUM ( Backorders1[Precio BackOrder MXP] ),
        FILTER ( Backorders1, Backorders1[CW] = MaxCW )
    )

Alternatively you could put all the logic in one measure in a slightly more efficient way (without filtering the entire table) like this:

Back Order Amount =
VAR MaxCW =
    CALCULATETABLE ( LASTNONBLANK ( Backorders1[CW], 0 ), ALLSELECTED () )
RETURN
    CALCULATE ( SUM ( Backorders1[Precio BackOrder MXP] ), KEEPFILTERS ( MaxCW ) )

The problem with the original measure was the context where [Max CW] was being evaluated (within the row context of FILTER within CALCULATE), meant ALLSELECTED(...) couldn't restore the overall filter context of the table.

Regards,

Owen

[calerof]

Hello Owen,

Thank you very much for your help. Great advice! Now's working just correct.

Kind regards,

Fernando