Forum Discussion

MagikJukas's avatar
MagikJukas
Resolver 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 th...
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)))
v-yadongf-msft's avatar
v-yadongf-msft
Community 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
    Resolver 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
      Community 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.

      • MagikJukas's avatar
        MagikJukas
        Resolver III
        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)))