Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Remove current month from accumulated

Hi!! 

i would like to how to calculate the current year, without adding the current month in the result (doing it would be, adding only January, February 2022, isolating March 2022).

 

I already have the measure with the sum of the table column I want and I also already have the dcalender [Date].

 

 I tried with TOTALYTD but I couldn't remove the current month.

 

I imagine it's something like this:

CALCULATE(TOTALYTD(sumsales),dcalender[date], - Current month.

Thanks!! I'm Brazilian, forgive me for any language mistakes.

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file. I hope the below solution provide an idea what to implement to your data model.

     

     

    Sales total: = 
SUM( Sales[Sales] )

     

    Sales YTD: = 
CALCULATE ( [Sales total:], DATESYTD ( 'Calendar'[Date] ) )

     

    Sales YTD until last month: =
VAR lastmonthendofday =
    EOMONTH ( TODAY (), -1 )
VAR result =
    CALCULATE (
        [Sales YTD:],
        KEEPFILTERS (
            FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= lastmonthendofday )
        )
    )
RETURN
    result

     

  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , try like. comments are additional option to check

     

    YTD QTY forced=
    var _max = eomonth(today(),-1)
    return
    if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
    //or
    //calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file. I hope the below solution provide an idea what to implement to your data model.

     

     

    Sales total: = 
SUM( Sales[Sales] )

     

    Sales YTD: = 
CALCULATE ( [Sales total:], DATESYTD ( 'Calendar'[Date] ) )

     

    Sales YTD until last month: =
VAR lastmonthendofday =
    EOMONTH ( TODAY (), -1 )
VAR result =
    CALCULATE (
        [Sales YTD:],
        KEEPFILTERS (
            FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= lastmonthendofday )
        )
    )
RETURN
    result

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tested here and got it!! Thank you very much Jihwan_Kim 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , try like. comments are additional option to check

     

    YTD QTY forced=
    var _max = eomonth(today(),-1)
    return
    if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
    //or
    //calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tested here and got it!! Thank you very much amitchandak