Get each month total and average based on date

[usomaraju]

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

[sturlaws]

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.

[usomaraju]

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

[sturlaws]

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

[usomaraju]

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

[sturlaws]

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

[usomaraju]

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

[usomaraju]

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

[sturlaws]

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

Is it a calculated column?

[usomaraju]

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

[sturlaws]

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

[usomaraju]

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

[sturlaws]

Have a look at the attached pbix-file

-s

[usomaraju]

hi sturlaw,

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

can u please upload it again .

Thank you

[Anonymous]

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

Best
D

[sturlaws]

strange. see if works now

[usomaraju]

still same sturlaw, not able to open the file.

i'm not sure why.

[usomaraju]

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

[sturlaws]

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