Forum Discussion

hcirak's avatar
hcirak
Frequent Visitor
3 years ago
Solved

Dateadd Grand Total

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's avatar
    tamerj1
    3 years ago

    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 ) )
    )
    )

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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 ) )
    )
    )