Simple DAX Count Problem

I am amending my original post. I am using the following DAX code to count the number of tickets closed in the last 7 days.

Tickets Closed Last 7 Days = CALCULATE(COUNTROWS('Service Desk Tickets Closed'), FILTER('Service Desk Tickets Closed', 'Service Desk Tickets Closed'[Closed Date Time] >= [End Date Minus 7])).

Closed Date Time is in date/time format and is in the table. End Date Minus 7 is in Date/Time format and is a Measure. Where this is failing is Closed Date Time is always greater than End Date Minus 7 so it counts every row. There are several months of dates in the table going back to January. The End Date Minus 7 is the 7th of May 2019. I verified that. Am I not able to compare dates this way?

Please provide a sample PBIX file so we can better assist you in solving this question.


2019 BI Service Desk.pbix (1.1 MB)

First thing here is to never use date/time formatting inside dax formulas. It confuses time intelligence functions. So you want to always reference columns which are just dates.

What you need to do if you actually need a break out of exact hours is you need to have a seperate column for hours (and another one for minutes) if you need it to that detail as well.

You can do this easily in the query editor

When working these out, always start with information in a table, so you can see the numbers and see how they are calculating in the current context.

You could also use this to calculate total tickets

Total Tickets = 
COUNTROWS( 'Service Desk Tickets Closed' )

Then for last 7 days, not too difficult from here…it’s basically a running total from what I can gather.

7 Day Running Total = 
CALCULATE( [Total Tickets],
    DATESINPERIOD( Dates[Date], MAX( Dates[Date] ), -7, DAY ) )


I’ve attached the model
2019 BI Service Desk.pbix (1.1 MB)

Also one other thing,

I would really look to make small improvement on how this model looks. I can be somewhat confusing in this shape and structure.

See here for all my best practice tips


1 Like

This reply got me to think about the problem differently…which is what I need as this stage. Thank You.

1 Like

Hi all I am struggling to calculate a date from a date minus number of days elapsed.

I want to calculate the date using today minus days elapsed from a column in the date so that I show a date in my visualisation rather than days elapsed!

Can someone help me out please! I can do it in Excel using Today() - cell ref but can’t get PowerBI to return the date I require!!

Hi @ray. Can you please post a work-in-progress PBIX file (sample data if necessary) and an Excel mock-up of your desired outcome? These will help us identify and resolve your issue; sounds like something small. Greg

Edit: just saw you made a new post for this issue, which I meant to suggest, but you beat me to it :slight_smile:

HI Greg, yes I realised I had posted in the wrong location! Thanks for your help.