Dateadd Grand Total

[hcirak]

Hello all,

I have a powerbi report below, that is very simple that consists of two columns: Quantity and Date. 

I have one relationship between Sales and Date, on Date column.

I want to calculate two measures. First, Sales Amount.

Sales Amount = SUM([Sales])

Second, I want to calculate a formula for previous month same period. I mean, when I select the first 13 days of February, it should calculate the formula for the first 13 days of January. My calculation is below:

Previous Month Same Period =

VAR LDS =

    LASTNONBLANK ( 'Date'[Date], Sales[Sales Amount] )

VAR CR =

    DATESBETWEEN ( 'Date'[Date], MIN ('Date'[Date] ), LDS)

VAR PR =

   DATEADD(CR,-1,MONTH )

RETURN

    IF (

      LDS >= MIN ( 'Date'[Date] ),

        CALCULATE ( Sales[Sales Amount] , PR )

    )

In this formula, row context is true but in grand total is false. 

When I select the first 13 days of February, it gives me the wrong result in grand total. But if I select the first 12 days of February, it gives me correct grand total.

 Could you please help me on that? 

Thanks in advance.

[tamerj1 · Accepted solution]

Hi @hcirak 

please try

Previous Month Same Period =
SUMX (
VALUES ( 'Date'[Date] ),
CALCULATE (
VAR LDS =
LASTNONBLANK ( 'Date'[Date], Sales[Sales Amount] )
VAR CR =
DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), LDS )
VAR PR =
DATEADD ( CR, -1, MONTH )
RETURN
IF ( LDS >= MIN ( 'Date'[Date] ), CALCULATE ( Sales[Sales Amount], PR ) )
)
)