cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aristotle
Regular Visitor

Total sum for consecutive period

Hello Dax gurus!, 

 

I have the count for each consecutive month  in the below screen shot. What i want to get is the values in the Total column. For each consMonth 1,2,3,4....., i want to add up the values in the count column. 
May 20 = 1

Jun 20 = 1+1

July 20 = 1 + 1 + 2

 

Aug and oct or not consecutive months so the total start again in Oct and so on. Here is the output i am expecting:

 

aristotle_0-1628944962774.png

thanks 

 

 

3 REPLIES 3
Jos_Woolley
Solution Sage
Solution Sage

Hi,

Total =
VAR ThisStartMonth = 'Table'[Startmonth]
VAR FirstMonth =
    CALCULATE (
        MAX ( 'Table'[Startmonth] ),
        FILTER (
            'Table',
            'Table'[Startmonth] <= ThisStartMonth
                && 'Table'[consMonths] = 1
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Count] ),
        FILTER (
            'Table',
            'Table'[Startmonth] >= FirstMonth
                && 'Table'[Startmonth] <= ThisStartMonth
        )
    )

Regards

Greg_Deckler
Super User
Super User

@aristotle I think you are going to have to start with a resetting counter and that is Cthulhu:

Cthulhu - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Love the choice of name! 😀

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors