Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm doing a social media report. The data I have, contains several facebook accounts, with id that differentiates each post. From the same post, I have a history of each update (comments, share, etc ..). The data is cumulative. The goal is to create measurements or columns or a new table, to calculate comments, share, etc. I want to show the calculations in a table and also in kpi.
I attach excel table as an example. The relationships to this table are with a time dimension, with the clients, with the accounts and with the platforms.
Thank you very much in advance.
PostID | CreateDate | Update | name | account | platform | PostText | Comments Accumulated | Shares Accumulated | Comments per Month | Shares per Month |
1 | 01/29/2018 8:00:00 | 01/29/2018 10:00:00 | clientA | clientA_1 | Lorem upsum… | 1 | 0 | |||
1 | 01/29/2018 8:00:00 | 01/29/2018 14:00:00 | clientA | clientA_1 | Lorem upsum… | 3 | 1 | |||
1 | 01/29/2018 8:00:00 | 01/29/2018 18:00:00 | clientA | clientA_1 | Lorem upsum… | 5 | 1 | |||
2 | 01/30/2018 9:00:00 | 01/30/2018 11:00:00 | clientA | clientA_1 | Lorem upsum… | 1 | 0 | |||
1 | 01/29/2018 8:00:00 | 01/30/2018 10:00:00 | clientA | clientA_1 | Lorem upsum… | 5 | 2 | |||
2 | 01/30/2018 9:00:00 | 01/30/2018 14:00:00 | clientA | clientA_1 | Lorem upsum… | 50 | 25 | |||
1 | 01/29/2018 8:00:00 | 01/30/2018 14:00:00 | clientA | clientA_1 | Lorem upsum… | 8 | 2 | |||
1 | 01/29/2018 8:00:00 | 01/30/2018 18:00:00 | clientA | clientA_1 | Lorem upsum… | 10 | 3 | |||
1 | 01/29/2018 8:00:00 | 01/31/2018 10:00:00 | clientA | clientA_1 | Lorem upsum… | 10 | 3 | |||
2 | 01/30/2018 9:00:00 | 01/31/2018 14:00:00 | clientA | clientA_1 | Lorem upsum… | 100 | 30 | |||
1 | 01/29/2018 8:00:00 | 01/31/2018 14:00:00 | clientA | clientA_1 | Lorem upsum… | 11 | 8 | |||
2 | 01/30/2018 9:00:00 | 01/31/2018 18:00:00 | clientA | clientA_1 | Lorem upsum… | 120 | 40 | 120 | 40 | |
1 | 01/29/2018 8:00:00 | 01/31/2018 18:00:00 | clientA | clientA_1 | Lorem upsum… | 11 | 8 | 11 | 8 | |
2 | 01/30/2018 9:00:00 | 02/15/2018 10:00:00 | clientA | clientA_1 | Lorem upsum… | 200 | 50 | 80 | 10 | |
1 | 01/29/2018 8:00:00 | 02/17/2018 10:00:00 | clientA | clientA_1 | Lorem upsum… | 12 | 10 | 2 | 2 | |
2 | 01/30/2018 9:00:00 | 03/15/2018 9:00:00 | clientA | clientA_1 | Lorem upsum… | 300 | 60 | 100 | 10 | |
2 | 01/30/2018 9:00:00 | 04/15/2018 9:00:00 | clientA | clientA_1 | Lorem upsum… | 450 | 75 | 150 | 15 | |
3 | 04/25/2018 9:00:00 | 04/25/2018 12:00:00 | clientA | clientA_1 | Lorem upsum… | 3 | 1 | 3 | 1 | |
2 | 01/30/2018 9:00:00 | 05/15/2018 9:00:00 | clientA | clientA_1 | Lorem upsum… | 500 | 100 | 50 | 25 | |
2 | 01/30/2018 9:00:00 | 06/15/2018 9:00:00 | clientA | clientA_1 | Lorem upsum… | 600 | 120 | 100 | 20 | |
2 | 01/30/2018 9:00:00 | 07/15/2018 9:00:00 | clientA | clientA_1 | Lorem upsum… | 800 | 135 | 200 | 15 |
Hi @DavidGTMS,
If I understand correctly, you would like to calculate below results, right?
1. last month Comments Accumulated
2. last month Share accumulated
3. difference between last month Comments Accumulated and current month Comments Accumulated
4. difference between last month Share Accumulated and current month Share Accumulated
As the sample data you provided in your initial post contains moth January and April, it hard to test it. To return previous month result, you could try PREVIOUSMONTH Function within a measure.
Reference:
https://community.powerbi.com/t5/Desktop/PREVIOUSMONTH/m-p/13313
Best Regards,
Qiuyun Yu
hi,
I tried "previous ....", but it gave me errors. Regarding the months, I have passed data for the months of January, February, March, April, May, June and July. Not only January and April.
The complexity, is that the total of the month is false, since it is an accumulated one. Really, it would have to be deducted from the total of the month the total of the last month.
If you do not see a solution, Thanks anyway.
hi,
What I'm looking to do is the following, check the accumulated for each post of the current month and subtract the accumulated from that post from last month if you have it. And in that comparison, it may be the case, that last month's post are no longer active in the current month, so their values for the last month should not be subtracted in the current month.
Attached screenshot of the error I suffer.
Hi,
Maybe there is some possibility to perform a summarize to filter the table, and filter only the most recent row of each end of the month. Thus, in the new table it would be easier to make measurements or calculated columns.
But neither is that previous step.
Thank you
I want to show the data in a table. The rows will have the columns "PostID", "CreateDate", "Name", "Account", "PostText" and the measures or columns calculated from the Comments and Share of the accumulated of the last month, of the accumulated of this month and of the difference with the previous month (current). I also want to show these 3 latest data, in some kpi cards and in a donut.
Thank you so much.
I attach examples of how I want to show the data
Hi @DavidGTMS,
Regarding the sample data you shared, what's the desired result you want?
Best Regards,
Qiuyun Yu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |