Calculate Average Based on Date Slicer

[Anonymous]

Hello!

I am trying to create a card that will average out the total count of Sales Activities and Leads based on the amount of days that someone inputs on the report date slicer. 

Activies and Leads are being pulled from 2 seperate SQL tables, So I wrote a measure that filters the person that created the Activity and filters the same person that created Lead. 

I wrote another measure that takes the addition measure from above and divides it by 10 (2 business weeks), which works as long as the slicer is selected for a 2 week time period. But I wanted to seek help to dynamically change the average based on the slicer. 

Thanks in advance.

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[Anonymous]

Hi @Anonymous ,

You can create a table of days, then put the field into the slicer, and use the SELECTEDVALUE function to reference.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Activity Summary Table:

Lead Summary table:

The slicers (there are 2, 1 for each createdate from each table. Slicers are synced) The create dates are sporadic - there is no set dates entered. 

I have a measure that counts the acitivity ID. It's only counted if it matches the name of the person who created the lead or acitivity. 

So from those slicers ... I need to find a way to take the total number of days from the start/end dates in the slicer.

[Anonymous · Accepted solution]

Hi @Anonymous ,

DATEDIFF function is the function you want. It returns the number of days between two dates.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @Anonymous ,

can you maybe give an example with numbers?

The description lets room for interpretation.

If you want to calculate something based on the selected values from a slicer the function ALLSELECTED might help you. But I don't fully understand your case.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic