Forum Discussion

ajmeyer's avatar
ajmeyer
Frequent Visitor
7 years ago
Solved

Calculating the Difference Between Running Totals

So I am trying to calculate a running difference between each month. I have a running count of users for an app and just trying to find the monthly growth. Is there an expression that can get this?

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi ajmeyer 

    Create a calendra table and create relationships between calendar table and your table.

    calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

    Create measures in your table

    running = CALCULATE(DISTINCTCOUNT(Sheet4[users]),FILTER('calendar','calendar'[Date]<=MAX('calendar'[Date])))

runing+1 = CALCULATE([running],FILTER(ALL('calendar'),DATEDIFF('calendar'[Date],MAX('calendar'[Date]),MONTH)=1))

monthly growth = IF([running]<>BLANK(),[running]-[runing+1])

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi ajmeyer 

    Create a calendra table and create relationships between calendar table and your table.

    calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

    Create measures in your table

    running = CALCULATE(DISTINCTCOUNT(Sheet4[users]),FILTER('calendar','calendar'[Date]<=MAX('calendar'[Date])))

runing+1 = CALCULATE([running],FILTER(ALL('calendar'),DATEDIFF('calendar'[Date],MAX('calendar'[Date]),MONTH)=1))

monthly growth = IF([running]<>BLANK(),[running]-[runing+1])

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.