Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
3 years ago
Solved

sum next selected month

Hello,

I have a column with sales and another with dates (YYYY.MM.1).

I have a slicer where I select dates.

 

I want to build a measure that sums sales for next month based on the selection on the slicer.

any idea?

thanks

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi MagikJukas ,

     

    Please create a measure:

    Next month sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Month] = SELECTEDVALUE('Table'[Month])+1))
     
    The measure will sum in the visualization.

     

    Best regards,

    Yadong Fang

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

  • MagikJukas's avatar
    MagikJukas
    3 years ago

    I am trying to use your suggestion, but I encountering some problem to calculate the sales.

    I created two Variables, selected month and the next month.

     

    Now I am trying to calculate the sales for next month.

    I did not put yet the variables in the return because I want to test it first.

    Assuming I selected November in the filter, I want to get the sales for December.

    Unfortunately, when I put the December date, it gives blank.

    Do you have an advice how to fix my return formula?

     

    Measure=
    var     Month_selected=(MAX(Table4[Date2]))

    var Next_Month=(eomonth(Month_selected,0)+1)

    return CALCULATE(SUM(Table4[Sales]),FILTER(ALLSELECTED(Table4[Date2]),Table4[Date2]=DATE(2022,12,1)))

6 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi MagikJukas ,

     

    This is my test table:

     

    Please create two columns:

     

    Month = MONTH('Table'[Date])

Next month sales = 
var cur_month = 'Table'[Month]
var nex_month = cur_month + 1
return
CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Month] = nex_month))

     

     

    I think this is the result you want:

     

    Best regards,

    Yadong Fang

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

    • MagikJukas's avatar
      MagikJukas
      Icon for Resolver III rankResolver III
      3 years ago

      Interesting solution.

      however you are using columns and in the visualization you do not sum but you display the max.

       

      this gives some problems in my dataset since I have several other columns that I filter. I need to sum in the visualization.

       

      Would it be possible to create a measure instead?

      • v-yadongf-msft's avatar
        v-yadongf-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi MagikJukas ,

         

        Please create a measure:

        Next month sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Month] = SELECTEDVALUE('Table'[Month])+1))
         
        The measure will sum in the visualization.

         

        Best regards,

        Yadong Fang

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    MagikJukas , Create a date table join with date of your table

     


    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))

     

    next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s