Forum Discussion

Zeesh1979's avatar
Zeesh1979
Regular Visitor
3 years ago
Solved

Counting rows

I have table that contains List of items and their sales count on date basis. i created a new column that contains month. now i want to have commulative sum of sales for each month. e.g. if in filter...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Zeesh1979 ,

    I made a simple file, please check the model:

     

    Total Sales = 
VAR _month = SELECTEDVALUE('Calendar'[Month])
VAR _total = CALCULATE(SUM('SalesTable'[Sales]),FILTER('SalesTable',_month>MONTH('SalesTable'[Date]))) + 0
RETURN
_total

     

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

Zeesh1979's avatar
Zeesh1979
Regular Visitor
3 years ago

how? can u plz elaborate

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Zeesh1979 Hard to be specific with the amount of information you provided. It should be something along the lines of:

Better RT = 
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
    SUMX(__Table,[Value])

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • Zeesh1979's avatar
    Zeesh1979
    Regular Visitor
    3 years ago
    ItemSalesMonth
    Cars100Jan
    Bikes150Feb
    Cars50Jan
    Bikes120March

     

    expected output if March filter selected

    Total Sales300

    expected output if Feb filter selected

    Total Sales150

     

    i hope this helps more...(im new to community and learning how to improve asking question)

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      Zeesh1979 Yeah, you are really going to want an actual Date column in there otherwise it's going to get really ugly to code because there is no way to numerically figure out "before" and thus you would end up hand coding all of your month values. Super ugly. You could possibly get away with a numeric Month column, 1, 2, 3, 4, etc. if you don't care about years.

  • Zeesh1979's avatar
    Zeesh1979
    Regular Visitor
    3 years ago

    surely i will add numeric month column but what would be the final solution?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Zeesh1979 ,

      I made a simple file, please check the model:

       

      Total Sales = 
VAR _month = SELECTEDVALUE('Calendar'[Month])
VAR _total = CALCULATE(SUM('SalesTable'[Sales]),FILTER('SalesTable',_month>MONTH('SalesTable'[Date]))) + 0
RETURN
_total

       

       

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly -- How to provide sample data