Forum Discussion

refint_650's avatar
refint_650
Icon for Helper I rankHelper I
4 years ago
Solved

Running total for month

Hello 

 

I' couldnt calculate for runningtotal for year/month/orderid. i tried some power bi .

In a 1st step how can i count & apply sum of Orderid's by month when year is selected and do runningtotal for month from calendar table.

 

Itried sample formul from but it doesnt seem working.

 

Sales YTD = var maxdate = MAX('Date'[Date])
Return CALCULATE(SUM(Sales[Tonnes]), All('Date'), 'Date'[Date]<= maxdate).
 
Thanks all
VS
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, refint_650 

     

    I think I can understand what you mean. Order Count is the Measure to calculate Order ID. How to calculate the cumulative summation of Order Count when Order Count is Measure.

     

    You can try the following methods.

    Maesure:

    Cumulative = 
CALCULATE (
    SUMX ( VALUES ( 'Table'[Month No] ), [Order Cou] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Month No] <= MAX ( 'Table'[Month No] ) )
)

    Order Cou in my table is Measure.

     

    Best Regards,

    Charlotte Zhang

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

5 Replies

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

    refint_650 , if you need cumulative , with help from date table

     

    CALCULATE(SUM(Sales[Tonnes]), filter(All('Date'), 'Date'[Date]<= max('Date'[Date] ) ) )

     

    if you need YTD

    CALCULATE(SUM(Sales[Tonnes]),datesytd('Date'[Date]))

     

     

    if you need MTD

    CALCULATE(SUM(Sales[Tonnes]),datesmtd('Date'[Date]))

    • refint_650's avatar
      refint_650
      Icon for Helper I rankHelper I
      4 years ago

      amitchandak 

       

      Appreciate for respone.

      My table has year, month ,date, order id. year slicer only.

      1st step  i did measure which count(orderid) -which calculates count of order id's per each month.

       i couldnt figure how to sum   jan +feb  counts in a cumulative  untill max month.

      thank all

       

      vs

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

        refint_650 , Count of order id should help

         

        CALCULATE(Count(Table[order id ]), filter(All('Date'), 'Date'[Date]<= max('Date'[Date] ) ) )

         

        If this does not help
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.