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

  • MagikJukas's avatar
    MagikJukas
    Icon for Resolver III rankResolver 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)))
    • MagikJukas's avatar
      MagikJukas
      Icon for Resolver III rankResolver III
      3 years ago

      ok, now I fixed it.

      So the solution was to use the variables to extract the values I wanted to consider.

       

      then in the return function, I used the Filter(ALL(Date)...) to exclude in the fucntion any date selected within a slicer. I then told to set date equal to the variable next_Month and it worked.

       

      CALCULATE(SUM([Sales]),FILTER(ALL([Date]),[Date]=Next_Month))