Forum Discussion

admin11's avatar
admin11
Memorable Member
4 years ago

My Current Month -2 REV expression return wrong value ?

Hi All

 

Below expression is working fine for YTD amount :-

 
AMOUNT = SUM(GL[AMT])*-1

_YTD_REV = CALCULATE(CALCULATE(GL[AMOUNT],DATESYTD('Date'[Date],"12/31")), GL[1_REV] = "REV")

 

Below expression is also working fine for QTD amount :-

QTD REV = CALCULATE(GL[AMOUNT],DATESQTD(dateadd('Date'[Date],-1,QUARTER)), GL[1_REV] = "REV")

 

 

Below expression is to return Last month Rev amount , is  not working , because it return wrong value  :-

 

AMOUNT_REV =
CALCULATE(
SUM(GL[AMT])*-1,
FILTER(
GL,
GL[1_REV]="REV"
)
)
 
Current month -2 REV =
VAR _Start = EOMONTH ( TODAY (), -3 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
CALCULATE ( [AMOUNT_REV], DATESBETWEEN ( 'Date'[Date], _Start, _End ) )
 
Hope some can share with me where go wrong ?
 
Hints :-
I have applied all above for EXP ,it is working fine . only REV i got problem..
 
AMOUNT_EXP =
CALCULATE(
SUM(GL[AMT])*-1,
FILTER(
GL,
GL[1_EXP]="EXP"
)
)
 
Current month -2 EXP_ =
VAR _Start = EOMONTH ( TODAY (), -3 ) + 1
VAR _End = EOMONTH ( _Start, 0 )
RETURN
CALCULATE ( [AMOUNT_EXP]*-1, DATESBETWEEN ( 'Date'[Date], _Start, _End ) )
 
The below expression Current month -2 Exp is return correct value :-
AMOUNT = SUM(GL[AMT])*-1
Current Month -2 EXP = CALCULATE(GL[AMOUNT],eomonth('Date'[Date],0) = eomonth(today(),-1),GL[1_EXP] = "EXP")
 
Paul
 
Need Help

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi admin11 

    Thanks for reaching out to us.

    1. The period your measure returns is :

    2. if you want to put SUM statement in a measure, then nest it into another,  you can try this,

    sum = SUM ( GL[AMT] ) * -1
    Current month -2 REV = 
VAR _Start =
    EOMONTH ( TODAY (), -3 ) +1
VAR _End =
    EOMONTH ( _Start, 0 )
RETURN
    CALCULATE(  [sum],DATESBETWEEN ('Date'[date], _Start, _End ), GL[1_REV] = "REV" )

    result

     

    Best Regards,

    Community Support Team _Tang

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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi admin11 

    Hope you are doing well.
    Can we close this thread now if there is no other question? It would be nice if you can accpet my answer as the solution. Thanks 🙂

     

    Best Regards,

    Community Support Team _Tang

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