Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vzbkb1
Helper I
Helper I

Running Total by date counting rows

 

I have two tables:

Table 1:  Calendar table. The calendar table is linked to the tickets table with the

 

Table 2:  Tickets table  called “Jira” as below

 

 

vzbkb1_0-1676995733398.png

 

 

 

I want to obtain a running table that will look like:

 

vzbkb1_1-1676995733400.png

 

 

It will give the running accumulate of the tickets created, those with Date done, and those with Date Validated

 

With this formula I was able to get the running total of the Created one, but I am not able to get the other two

Open Tickets Accumulated = CALCULATE(

    COUNTROWS(Jira),

    FILTER(

        ALL(Calendar[Date]),

        Calendar[Date] <= MAX(Jira[Date Created])

    )

)

 

Can you help me?

3 REPLIES 3
vzbkb1
Helper I
Helper I

It is not working as expected.  this is what I am getting

vzbkb1_0-1676998142792.png

At the same time as I will like to filter by name, for example if I filter by Abel I am getting

vzbkb1_1-1676998216276.png

Which is what I am expecting

 

What do I need to update?

 

 

Greg_Deckler
Super User
Super User

@vzbkb1 Try:

Tickets Done Accumulated =
  VAR __Date = MAX('Jira'[Date Created])
  VAR __Table = FILTER(ALL('Jira'), [Date Done] < __Date)
  VAR __Result = COUNTROWS(__Table)
RETURN
  __Result


Tickets Done Validated =
  VAR __Date = MAX('Jira'[Date Created])
  VAR __Table = FILTER(ALL('Jira'), [Date Validated] < __Date)
  VAR __Result = COUNTROWS(__Table)
RETURN
  __Result

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

It is not working as expected.  this is what I am getting

vzbkb1_0-1677065288517.png

 

At the same time as I will like to filter by name, for example if I filter by Abel I am getting

vzbkb1_1-1677065288348.png

 

Which is what I am expecting

 

What do I need to update?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.