Calculate Sum of orders until the last week Sunday.

[Jmenas]

Hi All,

I am trying to add a measure that can calculate a Measure (or column) that allows me to calculate the sum of orders from the first of September to the last Sunday (10th of September). The idea that it does it every week the same (dynamic). 

As you can see my data is like this:

My current Formula, although looks right on the table (22032), is giving me the right value (14M).

Formula:

Monthly Orders = CALCULATE(
SUM('Dailyorders'[orders]);
DATESBETWEEN(Datetable[Date];
STARTOFMONTH(Datetable[Date]);
(LASTDATE(Datetable[Date])-WEEKDAY(LASTDATE(Datetable[Date]);2))))

Not sure what is missing.

Best,
J

[v-jiascu-msft · Accepted solution]

@Jmenas,

Hi J,

The cause is that the third parameter "end_date" bigger than the second parameter "start_date", which cause the "Datesbetween" return null.

Try this formula please:

Monthly Orders 2 =
VAR endOfLastWeek =
    LASTDATE ( 'Date'[Date] ) - WEEKDAY ( LASTDATE ( 'Date'[Date] ), 2 )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Quantity] ),
        DATESBETWEEN (
            'Date'[Date],
            STARTOFMONTH ( 'Date'[Date] ),
            IF (
                STARTOFMONTH ( 'Date'[Date] ) > endOfLastWeek,
                endOfLastWeek + 7,
                endOfLastWeek
            )
        )
    )

Best Regards!

Dale

[Jmenas]

Hi @v-jiascu-msft,

I just try the calculation. I adapted but still, I get the 14M. I guess the idea will be to limit the measure just for this month. For me, the date period before is not relevant.

I will edit the code as soon as I  have a solution.

Best,
J.

[TomMartens]

Hey,

if you use the measure that works on the table from a card the following happens.

Due to the fact, that there is no active filter from your datetable, this is the difference in contrast to your table visual, the DAX function LASTDATE() will return the last member of your datetable, whereas the function STARTOFMONTH() will return the 1st date of your calendar table.

Regards

Tom