cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Measure for Count of opened items during last 30 days

I am looking for DAX that will count the number of Opened tasks during the last 30 days and 90 days. 

 

I have this simple table below

 

TaskCreated
Opened 01-Dec
Closed02-Dec
Closed02-Dec
Closed04-Dec
Opened 05-Dec
Opened 06-Dec
Opened 07-Dec

 

I will use Card to display this count

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Measure for Count of opened items during last 30 days

@ymirza

 

Try this:

 

Last 30 days =
CALCULATE (
     COUNT( Table[Task_ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[date]
                   >= MAX ( Table[date] ) - 30
                   && Table[date] <= MAX ( Table[date] )
                   && Table[Task] = BLANK()
                           )
)

 

OR 

 

Last 30 days =
CALCULATE (
     COUNT( Table[Task_ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[date]
                   >= MAX ( Table[date] ) - 30
                   && Table[date] <= MAX ( Table[date] )
                   && LEN[Task] = 0
                           )
)

View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
Memorable Member

Re: Measure for Count of opened items during last 30 days

@ymirza

 

try this formula

 

Last 30 days =
CALCULATE (
     COUNT( Table[Task_ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[date]
                   >= MAX ( Table[date] ) - 30
                   && Table[date] <= MAX ( Table[date] )
                   && Table[Task] = "Opened"
                           )
)

 

Highlighted
Helper I
Helper I

Re: Measure for Count of opened items during last 30 days

@themistoklis, thank for quick response. What is the Table[value] you are using from? i dont have any such column

Highlighted
Memorable Member
Memorable Member

Re: Measure for Count of opened items during last 30 days

@ymirza

 

I have amened the previous formula,

 

I have put task_Id instead of value and count instead os SUM.

In other words this is the place where you count the number of tasks.

I dont know which field identifies the tasks so i have just put Task_ID (which you can replace with the relevant field)

Highlighted
Helper I
Helper I

Re: Measure for Count of opened items during last 30 days

Thanks @themistoklis, it is working now. Can you also amend for values returning Blank?

Highlighted
Memorable Member
Memorable Member

Re: Measure for Count of opened items during last 30 days

@ymirza

 

Try this:

 

Last 30 days =
CALCULATE (
     COUNT( Table[Task_ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[date]
                   >= MAX ( Table[date] ) - 30
                   && Table[date] <= MAX ( Table[date] )
                   && Table[Task] = BLANK()
                           )
)

 

OR 

 

Last 30 days =
CALCULATE (
     COUNT( Table[Task_ID] ),
              FILTER (
                   ALL ( Table ),
                  Table[date]
                   >= MAX ( Table[date] ) - 30
                   && Table[date] <= MAX ( Table[date] )
                   && LEN[Task] = 0
                           )
)

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors