Simple DAX Count Problem

DAX DAX Calculations
1

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?

2

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

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

2019 BI Service Desk.pbix (1.1 MB)

4

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.

image
image1340×310 48.2 KB

You can do this easily in the query editor

image
image920×142 11.7 KB

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 ) )

image

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

Sam

1 Like
5

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

1 Like
6

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!!

8

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:

9

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