Forum Discussion
Helper IIGet each month total and average based on date
Helper IIhi 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
Resident RockstarHave 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
- 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
To open the file just update your PBI Desktop to the latest version.
Best
D