Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to recreate the following table that I have completed in excel in PowerBI now.
12 Month Due | 12 Month Complete | Portfolio % | |
2015 - 6 | 2846 | 2765 | 97.2% |
2015 - 7 | 2791 | 2733 | 97.9% |
2015 - 8 | 2696 | 2618 | 97.1% |
2015 - 9 | 2583 | 2518 | 97.5% |
The 12 Month Due and 12 Month Complete are looking at all the results from 12 months back of the date in the first column, I am unable to find a way of totalling a column this way in Power BI.
This is my formula in excel: =COUNTIFS('GAS Data'!$I:$I,">="&'GAS Data'!$Z8,'GAS Data'!$I:$I,"<"&'GAS Data'!$Z20)
or in simplified terms: =COUNTIFS(Dates,">=01/03/2015",Dates,"<01/04/2016")
I feel this shouldn't be complicated but I can only seem to be able to return the count of results from the month in the first column but not any of the 11 months previous to that date.
This is the results I am getting in Power BI:
Any help would be greatly appreciated.
Thanks,
Please take a look at this simple sample. I assume we have a dataset like below (have relationship with another calendar table), and we want to count the days from 12 months back.
A measure with following DAX formula should work.
I’ve also upload my testing .pbix file here. Please let me know if it is not the result you wanted.
Count = CALCULATE ( COUNTROWS ( 'Table' ), DATESBETWEEN ( 'Calendar'[Date], FIRSTDATE ( DATEADD ( 'Calendar'[Date], -11, MONTH ) ), LASTDATE ( 'Calendar'[Date] ) ) )
Best Regards,
Herbert
Thanks for getting back herbert.
This isn't quite working for me (could well be me not understaing something), on each day there can be multiple results as shown below:
targ_comp_dt | completed_dt |
27/01/10 | 25/01/10 |
27/01/10 | 27/01/10 |
29/01/10 | 29/01/10 |
29/01/10 | 26/01/10 |
31/01/10 | 23/01/10 |
02/02/10 | 15/02/10 |
02/02/10 | 01/02/10 |
02/02/10 | 11/02/10 |
02/02/10 | 27/01/10 |
03/02/10 | 23/02/10 |
03/02/10 | 19/02/10 |
I am able to count the number of results for each month but not the previous months still.
For example say there is this amount of results each month:
Jan15 - 248
Feb15 - 323
Mar15 - 198
Apr15 - 211
May15 - 133
Jun15 - 344
If I wanted the previous 3 months and my month column was June 2015 I would want to count the results in each of these columns from Apr15 - Jun15 so I would get 688 results, likewise for May15 I would want the results from Mar15 - May15 so I would get 542.
I feel I should be able to work this out from your formula but I can't seem to get it right.
Would I potentially have to create another table with the total results for each day/month in order to achieve this?
Any ideas?
Thanks,
Andrew.
The formula I provided before should also can be used when there are multiple results on each day. Did you create a calendar table and relate to your table with Date key?
Count = CALCULATE ( COUNTROWS ( 'Table' ), DATESBETWEEN ( 'Calendar'[Date], FIRSTDATE ( DATEADD ( 'Calendar'[Date], -2, MONTH ) ), LASTDATE ( 'Calendar'[Date] ) ) )
Best Regards,
Herbert
Hello Herbert,
Thanks for getting back again, I'll have another go at this other the next couple of days and let you know how I get along. I didn't create a relationship between the tables before which is where I am guessing I went wrong(Only been using PowerBI for a couple of weeks so still getting used to it).
Thanks for speedy response.
Andrew.
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |