Average total by day-number of each month for a measured total.

[Anonymous]

Hi,

I have a dataset of starters , with their true start date, and also the date that they paid a deposit.

e.g.

Start Date                   Deposit Date                           Calendar Month        Starter

2022-06-02 00:00:00 2022-06-02 05:00:25.7480000 2022-06-01 00:00:00 1
2022-06-02 00:00:00 2022-05-26 09:15:02.4466667 2022-06-01 00:00:00 1
2022-06-02 00:00:00 2022-05-24 08:23:22.0766667 2022-06-01 00:00:00 1
2022-05-26 00:00:00 2022-05-11 14:06:23.9666667 2022-05-01 00:00:00 1
2022-05-26 00:00:00 2022-05-13 12:46:03.2166667 2022-05-01 00:00:00 1
2022-05-26 00:00:00 2022-05-20 09:10:57.9300000 2022-05-01 00:00:00 1

I use the following DAX to calculate, from each date , how many starters are 'known about' in the month from how many deposits are in the system as of that date which also stops data returning from the current date onwards, and accounts for starters on the day into the totals:

Starter Total =

IF(

MAX('dim_DateReference'[Date]) > TODAY(),

BLANK(),

IF(MAX('dim_DateReference'[Date]) = TODAY(),

CALCULATE(COUNT('PBI Starter'[Deposit Date]), ALL('dim_DateReference'[Day Number]),

'PBI Starter'[Deposit Date] <= MAX('dim_DateReference'[Date]) + 1, 'PBI Starter'[Start Date] <= EOMONTH(MAX('dim_DateReference'[Date]),0))

,CALCULATE(COUNT('PBI Starter'[Deposit Date]), ALL('dim_DateReference'[Day Number]),

'PBI Starter'[Deposit Date] <= MAX('dim_DateReference'[Date]), 'PBI Starter'[Start Date] <= EOMONTH(MAX('dim_DateReference'[Date]),0))

))

I need to calculate, for each day number of the month, on average how many starters are 'known about'.

I.E on day one of the month, we have 20 known starters for the month, on average we should know about 24 so we need to improve to meet monthly targets. I also have to plot the average by day against the current month actual.

Thanks in advance.

[v-cazheng-msft]

Hi @Anonymous,

Not quite clear about your needs. Is this what you want? Average the Starter total by Month and Day number?

AvgStarters =
VAR maxDays =
    CALCULATE (
        MAX ( dim_DateReference[Day number] ),
        ALLEXCEPT ( dim_DateReference, dim_DateReference[Month] )
    )
RETURN
    'PBI Starter'[Starter Total]
        / ( maxDays - SELECTEDVALUE ( dim_DateReference[Day number] ) )

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Anonymous]

Here is the format of the desired data. In this visual the series has been filtered with a date slicer to just show the June data, showing from each day, how many starters for the month were recorded in the system using the deposit date. I would like in adition to show an average series, for example showing on average how many starters were on the system on day 7 of the month, as a performance metric.

[lbendlin]

Please confirm that there is no active connection in your data model between the fact table and the dim_DateReference table.

[Anonymous]

For the purpose of displaying day number on the x axis of visuals, there is an active relationship between fact.start date, and date.date

[lbendlin]

That will not work for dates without data unless you enable that setting and filter your calendar table.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Here is some better sample data:

Start Date	Deposit Date	Starter
09/06/2022 00:00	01/06/2022 10:02	1
09/06/2022 00:00	26/05/2022 15:42	1
09/06/2022 00:00	25/05/2022 10:09	1
09/06/2022 00:00	09/06/2022 05:00	1
09/06/2022 00:00	09/06/2022 05:00	1
09/06/2022 00:00	31/05/2022 10:05	1
09/06/2022 00:00	31/05/2022 13:33	1
09/06/2022 00:00	25/05/2022 11:18	1
09/06/2022 00:00	06/06/2022 14:15	1
09/06/2022 00:00	06/06/2022 20:10	1
09/06/2022 00:00	06/06/2022 19:16	1
09/06/2022 00:00	30/05/2022 11:23	1
09/06/2022 00:00	01/06/2022 11:42	1
09/06/2022 00:00	24/05/2022 10:59	1
02/06/2022 00:00	25/05/2022 12:08	1
02/06/2022 00:00	18/05/2022 13:33	1
02/06/2022 00:00	26/04/2022 10:16	1
02/06/2022 00:00	23/05/2022 11:37	1
02/06/2022 00:00	02/06/2022 05:00	1
02/06/2022 00:00	25/04/2022 11:45	1
02/06/2022 00:00	18/05/2022 11:48	1
02/06/2022 00:00	24/05/2022 08:23	1
02/06/2022 00:00	26/05/2022 09:15	1
26/05/2022 00:00	26/05/2022 05:00	1
26/05/2022 00:00	20/05/2022 09:10	1
26/05/2022 00:00	19/05/2022 09:10	1
26/05/2022 00:00	17/05/2022 15:39	1
26/05/2022 00:00	12/05/2022 14:44	1
26/05/2022 00:00	26/05/2022 11:14	1
26/05/2022 00:00	17/05/2022 09:41	1
26/05/2022 00:00	17/05/2022 13:15	1
26/05/2022 00:00	13/05/2022 15:31	1
26/05/2022 00:00	19/05/2022 12:57	1
26/05/2022 00:00	16/05/2022 12:43	1
26/05/2022 00:00	12/05/2022 15:20	1
26/05/2022 00:00	24/05/2022 09:52	1
26/05/2022 00:00	11/05/2022 14:06	1
26/05/2022 00:00	13/05/2022 12:46	1

As you can see there can be multiple starters on the same day, however they have different deposit dates and so will have been recorded onto the system at different dates.

Here is the format of the desired data. In this visual the series has been filtered with a date slicer to just show the June data, showing from each day, how many starters for the month were recorded in the system using the deposit date. I would like in adition to show an average series, for example showing on average how many starters were on the system on day 7 of the month, as a performance metric.