Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following issue which I would very much appreciate assistance with.
I have the following output in BI
FY Year | FY Month | Active | New | New Last 12 months |
2017 | P1 | 90870 | 1184 | 15531 |
2017 | P2 | 89541 | 1667 | 15892 |
2017 | P3 | 89199 | 668 | 15211 |
2017 | P4 | 87992 | 1524 | 15184 |
2017 | P5 | 87537 | 776 | 14853 |
2017 | P6 | 86598 | 1316 | 14680 |
2017 | P7 | 86042 | 923 | 13967 |
2017 | P8 | 85403 | 1004 | 13828 |
2017 | P9 | 84948 | 873 | 13417 |
2017 | P10 | 84458 | 881 | 13199 |
2017 | P11 | 83790 | 1013 | 12798 |
2017 | P12 | 83422 | 772 | 12601 |
Where New is the sum of a column in my fact table and last 12 months is calculated by a measure
Total New Last12months = CALCULATE ( [New], DATESBETWEEN ( 'Date Dimension'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date Dimension'[Date] ) ) ), LASTDATE ('Date Dimension'[Date] ) ) )
As this is a subscription base each row in the fact table represents 1 subscription with dates for starting and finishing which can be used to work out whether each subscriotion is active or closed at a given date and this gives the correct answer.
I now need to average the active total at the end of each month and was hoping to use a similar code to the above but as it is using a measure not a column this is not possible.
Does anyone have any ideas as to how to achieve this? I am toying with the idea that these numbers will need to be stored in a new table but am not sure as to the best approach.
Many Thanks
Richard
Hi @Greenwoodr ,
Could you please share the expected result and more information about this problem to us? It would be better if you can share some image to explain it. I think I cannot understand your requirement well.
Best Regards,
Teige
Hi.
Thanks for the offer of help. I have managed to do this now using the summarise function and putting the results in a new table
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |