topic Re: Get each month total and average based on date in DAX Commands and Tips

Get each month total and average based on date

usomaraju — Tue, 23 Jun 2020 04:01:25 GMT

hello All,

I need help on calculating the monthly total and average for the data.

i have the data like below

ID	Date	Days
163	2/28/2020 7:37	3
164	2/25/2020 12:47	0
165	3/2/2020 15:43	5
166	3/3/2020 4:09	6
173	3/3/2020 12:35	0
174	3/19/2020 19:46	16
175	3/19/2020 19:50	16
178	3/4/2020 14:27	0
179	3/4/2020 14:34	0
180	3/4/2020 14:36	0
181	3/4/2020 15:59	2
182	3/4/2020 15:59	8
185	3/10/2020 14:08	4
186	3/10/2020 14:08	4
187	3/10/2020 14:51	0
188	3/10/2020 14:53	3
189	3/11/2020 16:33	3
190	3/11/2020 16:41	0
191	3/12/2020 8:00	0
192	3/12/2020 8:01	0
195	3/12/2020 16:58	1
196	3/12/2020 17:01	1
197	3/13/2020 8:05	0
198	3/13/2020 8:06	0
199	3/13/2020 13:19	0
200	3/13/2020 13:10	0
201	3/27/2020 11:02	9
202	3/27/2020 11:02	15
204	4/16/2020 19:52	6
205	4/16/2020 19:49	6
206	5/6/2020 21:13	6
207	5/6/2020 21:12	6

need to get the output as below

Month	Total	Average
Jan-20	0	0
Feb-20	3	3
Mar-20	93	0.2
Apr-20	12	6
May-20	12	6
Jun-20	0	0
Jul-20	0	0
Aug-20	0	0
Sep-20	0	0
Oct-20	0	0
Nov-20	0	0
Dec-20	0	0

the calculation is for the month Jan 2020. i dont have any records, so the value is zero.

for the month feb 2020, i see two records (2/28/2020 and 2/25/2020 with valuse 3 and 0), here the total is 3+0=3

and the average is 3/1 = 3.

and also same for April, the total is 6+6 =12 and the average is 12/2 = 6.

Can anyone help me with this?

Thanks

Re: Get each month total and average based on date

sturlaws — Tue, 23 Jun 2020 09:37:30 GMT

Hi usomaraju

have a look at the attached .pbix-file

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Re: Get each month total and average based on date

usomaraju — Tue, 23 Jun 2020 15:47:11 GMT

Hi Sturla,

Thank you for your quick response, but when i used same formulas i see same result for all months.

the steps i followed, on my table, by using measure i applied the below formula

Total number of days = var _s = SUM('Table'[Days]) return

IF(ISBLANK(_s),0,_s)

which is displaying same no of days as in total no of days

and same for average also.I 'm not sure where i missed.

thanks

Re: Get each month total and average based on date

sturlaws — Wed, 24 Jun 2020 07:33:06 GMT

Did you create a date-table? And a relationship between the date table and your main table?

Re: Get each month total and average based on date

usomaraju — Wed, 24 Jun 2020 13:55:31 GMT

yes sturlaw, i did.

created the date table and made 1:*relationship between the date table and data table.

but i have one question is, the date column that i shared in my data wast get it from the quick measure column , so i used the original date column for to make relationship,will it cause the issue?

or can i make relationship with quikcmeasure column? if yesy how can we do that?

thanks

Re: Get each month total and average based on date

sturlaws — Wed, 24 Jun 2020 15:45:27 GMT

It need to be a separate table. In the modelling tab, find the 'New table'-button:

and use this piece of dax:

Dates = CALENDAR(DATE(2019,1,1),DATE(2020,4,30))

-s

Re: Get each month total and average based on date

usomaraju — Wed, 24 Jun 2020 16:02:20 GMT

i did same way sturlaw for the date table.

but my doubt is in My data table i had quickmeasure date column where i need to make relationship with datetable date column.

i'm not sure where i did mistake

Re: Get each month total and average based on date

usomaraju — Wed, 24 Jun 2020 16:08:59 GMT

hi sturla

here is my dataset and the date completed is the quickmeasure of other columns in the same table

id	days	date completed
163	3	2/28/2020 7:37
164	0	2/25/2020 12:47
165	5	3/2/2020 15:43
166	6	3/3/2020 4:09
173	0	3/3/2020 12:35
174	16	3/19/2020 19:46
175	16	3/19/2020 19:50
178	0	3/4/2020 14:27
179	0	3/4/2020 14:34
180	0	3/4/2020 14:36
181	2	3/4/2020 15:59
182	8	3/4/2020 15:59
185	4	3/10/2020 14:08
186	4	3/10/2020 14:08
187	0	3/10/2020 14:51
188	3	3/10/2020 14:53
189	3	3/11/2020 16:33
190	0	3/11/2020 16:41
191	0	3/12/2020 8:00
192	0	3/12/2020 8:01
195	1	3/12/2020 16:58
196	1	3/12/2020 17:01
197	0	3/13/2020 8:05
198	0	3/13/2020 8:06
199	0	3/13/2020 13:19
200	0	3/13/2020 13:10
201	9	3/27/2020 11:02
202	15	3/27/2020 11:02
204	6	4/16/2020 19:52
205	6	4/16/2020 19:49
206	6	5/6/2020 21:13
207	6	5/6/2020 21:12

Re: Get each month total and average based on date

sturlaws — Wed, 24 Jun 2020 22:07:36 GMT

What do you mean by the date completed is the quickmeasure of other columns in the same table?

Is it a calculated column?

Re: Get each month total and average based on date

usomaraju — Wed, 24 Jun 2020 22:44:40 GMT

yes sturlaw.

in my table i have duplicate ID's with different dates like below, and i applied the quick measure for to get the max date for each id, which is the final result of my date column.

and with that quick measure column, i'm not able to make relationship with the date table date column, so did 1: * relationship with the original column.

can you please help me on getting the monthwise report .

Thanks

Re: Get each month total and average based on date

sturlaws — Wed, 24 Jun 2020 23:21:00 GMT

Create a new calculated column in your table like this:

completed date = VAR _currentID = CALCULATE ( SELECTEDVALUE ( 'table'[ID] ) ) RETURN CALCULATE ( MAX ( 'table'[date] ), FILTER ( ALL ( 'table' ), id = _currentID ) )

and change the measure [total number of days] to

Total number of days = var _s = calculate(SUM('Table'[Days],filter('table', 'table'[date]='table'[completed date]) return IF(ISBLANK(_s),0,_s)

Change the other measure the same way.

If it still does not work, please post a copy of your report

Re: Get each month total and average based on date

usomaraju — Thu, 25 Jun 2020 01:25:28 GMT

hi sturlaw,

Thank you for your quick response

here is the result that i get from the formula,if you see, i'm not sure what the values are displaying here for the month of april , may and june.

here is my need, i have the below sample data

ID	Days	Date
163	2.9	2/25/2020 0:00
163	2.9	2/28/2020 0:00
164	0.1	2/25/2020 0:00
165	5.2	3/2/2020 0:00
166	5.8	3/2/2020 0:00
166	5.8	3/3/2020 0:00
173	0	3/3/2020 0:00
174	16.3	3/3/2020 0:00
174	16.3	3/19/2020 0:00
175	16.3	3/3/2020 0:00
175	16.3	3/19/2020 0:00
178	0.3	3/4/2020 0:00
179	0.3	3/4/2020 0:00
180	0.3	3/4/2020 0:00
181	2.1	3/4/2020 0:00
182	7.6	3/4/2020 0:00
185	4.4	3/10/2020 0:00
186	4.4	3/10/2020 0:00
187	0	3/10/2020 0:00
188	3.2	3/10/2020 0:00
189	3.2	3/11/2020 0:00
190	0	3/11/2020 0:00

from here for each id, i need max date and the orresponding days.

ID	Days	Date
163	2.9	2/28/2020 0:00
164	0.1	2/25/2020 0:00
166	5.8	3/3/2020 0:00
173	0	3/3/2020 0:00
174	16.3	3/19/2020 0:00
175	16.3	3/19/2020 0:00
178	0.3	3/4/2020 0:00
179	0.3	3/4/2020 0:00
180	0.3	3/4/2020 0:00
181	2.1	3/4/2020 0:00
182	7.6	3/4/2020 0:00
185	4.4	3/10/2020 0:00
186	4.4	3/10/2020 0:00
187	0	3/10/2020 0:00
188	3.2	3/10/2020 0:00
189	3.2	3/11/2020 0:00
190	0	3/11/2020 0:00

from here i need to total and average per month

in my data, for january, i dont see any data, so the value is zero

and for month february, i see 2 results, 2.9+0.1 =3, the total is 3 and the average for the month is 3/2(based on no of id's) = 1.5 and the same for march sum = 64.2 and the count of id's is 15, so the average value is total/count of the id's

which 64.2/15=4.28

so the final report what i need is

	total	average
Jan-20	0	0
Feb-20	3	1.5
Mar-20	64.2	4.28
Apr-20	0	0
May-20	0	0
Jun-20	0	0
Jul-20	0	0
Aug-20	0	0
Sep-20	0	0
Oct-20	0	0
Nov-20	0	0
Dec-20	0	0

Can you help me please,

Thank you

Re: Get each month total and average based on date

sturlaws — Thu, 25 Jun 2020 09:32:24 GMT

Have a look at the attached pbix-file

-s

Re: Get each month total and average based on date

usomaraju — Thu, 25 Jun 2020 14:26:50 GMT

hi sturlaw,

i'm not able to open the file, get the error mesaage

can u please upload it again .

Thank you

Re: Get each month total and average based on date

sturlaws — Thu, 25 Jun 2020 20:21:39 GMT

strange. see if works now

Re: Get each month total and average based on date

usomaraju — Thu, 25 Jun 2020 21:23:35 GMT

still same sturlaw, not able to open the file.

i'm not sure why.

Re: Get each month total and average based on date

usomaraju — Fri, 26 Jun 2020 02:37:57 GMT

hi sturlaw,

the error i see when i try to open is 'error Object reference not set to an instance of an obj'.

and i read so manyy blogs which suggested to installe oracle 64 bit instead of 32 bit, i dont have anything on my PC.

can you send me the dax commands, what you used in the file for to get the final result if possible.

Thank you in advance

Re: Get each month total and average based on date

sturlaws — Fri, 26 Jun 2020 07:33:12 GMT

Calculated column in the table containing your data:

IsLastDateFlag = VAR _id = CALCULATE ( SELECTEDVALUE ( 'Table'[ID] ) ) RETURN IF ( 'Table'[Date] = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( ALL ( 'Table' ), 'Table'[ID] = _id ) ), 1, 0 )

measure:

Total number of days = VAR _s = CALCULATE ( SUM ( 'Table'[Days] ), FILTER ( 'Table', 'Table'[IsLastDateFlag] = 1 ) ) RETURN IF ( ISBLANK ( _s ), 0, _s )

measure:

Average number of days = VAR _s = CALCULATE ( COUNT ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[IsLastDateFlag] = 1 ) ) RETURN DIVIDE ( [Total number of days], _s, 0 )

Date table(create new table):

Dates = CALENDAR(DATE(2020,1,1),date(2020,12,31))

calculated columns in date table:

Month = FORMAT(Dates[Date],"MMMM")

YearMonth = year(Dates[Date])*100+MONTH(Dates[Date])

MonthNum = month(Dates[Date])

Sort Month by MonthNum

Re: Get each month total and average based on date

Anonymous — Fri, 26 Jun 2020 08:41:42 GMT

To open the file just update your PBI Desktop to the latest version.

Best
D