topic Re: COUNTING AVERAGE OF THE ACTIVE PERIOD in DAX Commands and Tips

COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 09:51:36 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 10:22:52 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 10:34:47 GMT

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.

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 10:36:56 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 10:51:07 GMT

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.

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 10:58:51 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 11:15:46 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 11:19:25 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 11:24:05 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 11:37:13 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 12:49:30 GMT

Oh my gosh tamerj1 !!!!
It works!!!! It works!!!! It works!!!!
Thank you, thank you. Amazing!
I'm very grateful for your help. Million thanx! 🙂

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 14:00:12 GMT

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.

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 14:16:39 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 14:30:08 GMT

Hi tamerj1,

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 15:03:06 GMT

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.

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 15:19:36 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 15:36:01 GMT

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,

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 15:48:38 GMT

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.

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

tamerj1 — Mon, 15 Aug 2022 15:52:57 GMT

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

Re: COUNTING AVERAGE OF THE ACTIVE PERIOD

Anonymous — Mon, 15 Aug 2022 15:56:36 GMT

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