Forum Discussion

Sportily's avatar
Sportily
Frequent Visitor
4 years ago
Solved

DATEDIFF from Filter

Hi PowerPeople!

 

Please go easy, I'm a relative newbie, pushing boundaries and learning fast!

 

I have a working PowerBI instance. 

I am looking to add a measure which can present on a card.

 

The page has a Date Filter, from-to.

 

The calculation for the measure I need is:

Number of DAYS between the 2 dates specificied in the filter.

Divided by the total figure from column 2 in the displayed table.

Multiplied by 7

 

The intention is that this creates an 'Average sessions per week' figure.

IF I use 'AVERAGE', then the result is the total from column 2, divided by the number of w/c lines displyed. 

So for Feb 2021, that would be 70 divided by 5 to give an average of 14.

BUT there are only 28 days in Feb, so I really want to divide by 28*7, to give a more accurate average figure for the month = 17.5.

 

Screenshot attached. 

My stumbling block has been working out how to DATEDIFF reference the dates in the filter.

 

Thanks for all your help.


Chris

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Sportily 

    If the date slicer is coming from a date table then

    Number of Days =

    COUNTROWS ( 'Date' )

    If date table is not available then create a virtual one and count its rows

    Number of Days =

    COUNTROWS(

    CALENDAR(MIN(Table[Date]), MAX(Table[Date]))

    )

2 Replies

  • Sportily's avatar
    Sportily
    Frequent Visitor
    4 years ago

    Brilliant, thats ace. Thank you so much. Worked a treat. Thanks for being so helpful.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Sportily 

    If the date slicer is coming from a date table then

    Number of Days =

    COUNTROWS ( 'Date' )

    If date table is not available then create a virtual one and count its rows

    Number of Days =

    COUNTROWS(

    CALENDAR(MIN(Table[Date]), MAX(Table[Date]))

    )