Forum Discussion

pjpreddy2's avatar
pjpreddy2
Frequent Visitor
8 years ago
Solved

DATES!!!!

Ok gang,

 

Here's what I'm trying to do.  I want to know the number of days using a column with date values, then get the average, then take teh # of days open and filter between specific day ranges (i.e. 30, 60, 90). 

 

So ideally I want to take that column and calculate how many days from the [Date_Opened] to today.  Then I'd like to calculate the average.

 

I did get

# of Days Open = datediff(MIN(tableS1[Date_Opened]),TODAY(),DAY) 

 

and 

Avg Days Open = CALCULATE(AVERAGEX(tableS1,[# of Days Open]),tableS1[Account])

 

then

120 Days = IF(tableS1[# of Days Open]<=120,DISTINCTCOUNT(tableS1[Account]),"") *here I figured that if there was an open account greater than 120, doing a distinctcount would sum up the number that filled that criteria. 

 

So these return a value I think is right but I wanted to see if this was correct. 

 

Another question is, if I get a good figure, will this work with a slicer? 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Tough to say without sample data. Seems reasonable. And sure, if you create a column where you classify things into buckets like 120, 90 ,60 etc. then that will work in a slicer. I would do that and then all you need is a simple count aggregation to get you the number in each bucket.

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Tough to say without sample data. Seems reasonable. And sure, if you create a column where you classify things into buckets like 120, 90 ,60 etc. then that will work in a slicer. I would do that and then all you need is a simple count aggregation to get you the number in each bucket.