COUNTING AVERAGE OF THE ACTIVE PERIOD

[Anonymous]

Hi, 

Average Sessions =

AVERAGEX (

    VALUES ( 'Calendar'[Date] ),

       COALESCE ( [SDR ID average per CP ID], 0 )

)

This is the formula that returns the results correctly, averaging the usage data including the days when the unit wasn’t in use, but the issue is that My calendar starts in 2020 so the formula calculates the Averages including 0’s from 2020 not when my ‘Connector’ was first used, and runs to Q4 2022, as per my calendar.

With this function I am calculating the SDR ID(unique session number) Average per CP ID (unit number) Average, per day;
Some days the items are not in use, so that’s why the coalesce calculation is needed.

I am filtering the above function by the column (FACT TABLE[Connector]).

What can be done, to calculate the average usage, including 0’s within the days since the Connector has started working, not calculating any day before nor after that?

Example of my FACT TABLE Data:

Thanks a lot,
J 

[tamerj1 · Accepted solution]

@Anonymous 
Thank you for your patience. Pleae try 

Average Sessions =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

[tamerj1 · Accepted solution]

@Anonymous 
Yes That is true. In fact I did not understand your logic or returning zero instead of blank. This how I would do it

Average Sessions =
AVERAGEX (
    SUMMARIZE (
        'Calendar',
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month],
        'Calendar'[Date]
    ),
    [SDR ID average per CP ID]
)

[tamerj1]

Hi @Anonymous 
Please refer to sample file with the solution https://www.dropbox.com/t/7MRcHUdlIDGXTHiL

Count Measur = 
VAR MinDate = CALCULATE ( MIN ( Sales2[Order Date] ), REMOVEFILTERS ( ) )
VAR MaxDate = CALCULATE ( MAX ( Sales2[Order Date] ), REMOVEFILTERS ( ) )
VAR FilteredDates = FILTER ( 'Date', 'Date'[Date] <= MaxDate && 'Date'[Date] >= MinDate )
VAR Result = 
    AVERAGEX (
        FilteredDates,
        COALESCE ( CALCULATE ( COUNT ( Sales2[CustomerKey] ) ), 0 )
    )
RETURN
    Result

[tamerj1]

@Anonymous 
Also Please try this version

Average Sessions =
AVERAGEX (
    SUMMARIZE (
        'Calendar',
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month],
        'Calendar'[Date]
    ),
    IF (
        NOT ISEMPTY ( CALCULATETABLE ( 'FACT TABLE' ) ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

[tamerj1]

Hi @Anonymous 

please try

Average Sessions =
VAR FirstDateWithData =
    CALCULATE ( MIN ( TableName[Start Date] ), REMOVEFILTERS () )
VAR Dates =
    FILTER ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] <= FirstDateWithData )
RETURN
    AVERAGEX ( Dates, COALESCE ( [SDR ID average per CP ID], 0 ) )

[Anonymous]

Hi tamerj1,
Thank you for that although it didn't work: I have changed the table name within the formula to match my table name: 

Average Sessions =

VAR FirstDateWithData =

    CALCULATE ( MIN ( 'FACT TABLE'[Start Date] ), REMOVEFILTERS () )

VAR Dates =

    FILTER ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] <= FirstDateWithData )

RETURN

    AVERAGEX ( Dates, COALESCE ( [SDR ID average per CP ID], 0 ) )

When I include the formula to the visualisation - this is what I see. 

[tamerj1]

@Anonymous 

Sorry my mistake. It is the <= sign that should be >=

Average Sessions =
VAR FirstDateWithData =
    CALCULATE ( MIN ( TableName[Start Date] ), REMOVEFILTERS () )
VAR Dates =
    FILTER ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] >= FirstDateWithData )
RETURN
    AVERAGEX ( Dates, COALESCE ( [SDR ID average per CP ID], 0 ) )

[Anonymous]

Hi tamerj1, 

Thank you very kindly for this, but it didn't change a thing. 
When I visualise this formula it still calculates everything, all the 0's from the beggining of my calendar 2020 including zeros in Q4 2022. 

[tamerj1]

@Anonymous 
Ok, Let's please try 

Average Sessions =
VAR FirstDateWithData =
    CALCULATE ( MIN ( 'FACT TABLE'[End Date] ), REMOVEFILTERS () )
VAR Dates =
    FILTER ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] >= FirstDateWithData )
RETURN
    AVERAGEX (
        Dates,
        IF (
            NOT ISBLANK ( 'Calendar'[Date] ),
            COALESCE ( [SDR ID average per CP ID], 0 )
        )
    )

[Anonymous]

Hi tamerj1, 

The formula looks great but I get the exact same result.

Would anything got to to with the fact that we are calculating the average number of SDR ID  per average number of CP ID, by when the Connector is in use? See if this information changes anything. 
Ssample of my FACT TABLE:

 Thank you so much, 
J

[tamerj1]

@Anonymous 
What kind of relationship do you have between the two tables? Can you share a screenshot?

[Anonymous]

Thank you tamerj1,
See below. I also have the DIM table, but I am not sure if it's required for our measurement.  

[tamerj1 · Accepted solution]

@Anonymous 
Thank you for your patience. Pleae try 

Average Sessions =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

[Anonymous]

Hi tamerj1, 

The formula definitely solved many issues, for example it solved the line chart, but sadly it doesn't calculates the averages correctly. The Q1 (The item started operating in March, so the function doesn't take the 1st 2 months into account), the same with Q3 - we only had July, but the function seems to be taking the average of all 3 months, whch skews the totals. 

I'm sorry to ask, but if you could take anoter look at this and see if you can figure this out. 
See an example.  

[tamerj1 · Accepted solution]

@Anonymous 
Yes That is true. In fact I did not understand your logic or returning zero instead of blank. This how I would do it

Average Sessions =
AVERAGEX (
    SUMMARIZE (
        'Calendar',
        'Calendar'[Year],
        'Calendar'[Quarter],
        'Calendar'[Month],
        'Calendar'[Date]
    ),
    [SDR ID average per CP ID]
)

[Anonymous]

Hi tamerj1, 

I thaught it worked but it didn't. 
It is now again not calculating the 0's when the items are not used between days. 
See the tables below. 
The one on the left representing the data with the help of that function, but what I need is t see the total as shown on the right. 
So the function should not calculate years, months days before it became operational, but it should count the 0's in between days. This is why I used the coalesce function earlier. 

[tamerj1]

@Anonymous 
Ok, let's try mix up both solutions

Average Sessions =
IF (
    NOT ISEMPTY ( 'FACT TABLE' ),
    AVERAGEX (
        SUMMARIZE (
            'Calendar',
            'Calendar'[Year],
            'Calendar'[Quarter],
            'Calendar'[Month],
            'Calendar'[Date]
        ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )
)

Also I noticed that your calendar table columns require proper sorting. You need to sort text columns using related numeric columns

[Anonymous]

Hi tamerj1, 
The formula again is not calculating the averages properly. 
Please let me know when you have enough of working on it. 

Thank you for your advise on the Cakendar Table. I will get that sorted. 
Best, 

J

[tamerj1 · Accepted solution]

Hi @Anonymous 
Would you like to connect perhaps with zoom or teams so we may look into it together? I have sometime now if you wish.

[Anonymous]

Yes, please, 
Thank you. Could you please send me your Teams invite? 
J

[tamerj1]

https://teams.microsoft.com/l/meetup-join/19:meeting_NmRiMjA5YWUtYTdmYi00MDhiLWJkYzEtMzYzNjAwNzBiM2N...

[Anonymous]

Hi tamerj1, 

Wow! Hats off to you Sir! Gosh, I've lots to learn. 
Huge thanks, with amazement! 
Thaaaanx! 😀