Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi i am stuck with some data does not feel optimal for power bi. i am having some trouble when i try and do anything with the data. I am pretty new to power bi and need some help to see in what direction i should look for information.
In my data here if i would want to take my "New users " under metric_name and remove the amount of "churn" to see how the diffrence for every day how should i go about?
metric_date | site | subscription_type | subscription_duration | metric_name | metric_value | ts |
2018 2 01 | SE | gross | users | 61456 | 2018-05-10 10:25 | |
2018 2 01 | SE | gross | Churn | 153 | 2018-05-10 10:25 | |
2018 2 01 | SE | gross | New users | 325 | 2018-05-10 10:24 | |
2018 2 01 | DE | gross | users | 19995 | 2018-05-10 10:25 | |
2018 2 01 | DE | gross | Churn | 53 | 2018-05-10 10:25 | |
2018 2 01 | DE | gross | New users | 112 | 2018-05-10 10:24 | |
2018 2 01 | NO | gross | users | 52468 | 2018-05-10 10:25 | |
2018 2 01 | NO | gross | Churn | 80 | 2018-05-10 10:24 | |
2018 2 01 | NO | gross | New users | 247 | 2018-05-10 10:24 | |
2018 2 02 | SE | gross | users | 60924 | 2018-05-10 10:25 | |
2018 2 02 | SE | gross | Churn | 107 | 2018-05-10 10:24 | |
2018 2 02 | SE | gross | New users | 329 | 2018-05-10 10:23 | |
2018 2 02 | DE | gross | users | 19827 | 2018-05-10 10:25 | |
2018 2 02 | DE | gross | Churn | 35 | 2018-05-10 10:24 | |
2018 2 02 | DE | gross | New users | 82 | 2018-05-10 10:23 | |
2018 2 02 | NO | gross | users | 52237 | 2018-05-10 10:25 | |
2018 2 02 | NO | gross | Churn | 62 | 2018-05-10 10:24 | |
2018 2 02 | NO | gross | New users | 193 | 2018-05-10 10:23 |
Solved! Go to Solution.
Hi @Bromley,
You can use below measure to calculate diff between two date:
Diff = VAR _name = SELECTEDVALUE ( Table1[metric_name] ) VAR _currentDate = MAX ( Table1[metric_date] ) VAR _previousDate = MAXX ( FILTER ( ALLSELECTED ( Table1 ), [metric_name] = _name && [metric_date] < _currentDate ), [metric_date] ) RETURN IF ( _previousDate <> BLANK (), CALCULATE ( SUM ( Table1[metric_value] ), Table1[metric_date] = _currentDate, VALUES ( Table1[metric_name] ) ) - CALCULATE ( SUM ( Table1[metric_value] ), Table1[metric_date] = _previousDate, VALUES ( Table1[metric_name] ) ) )
Regards,
Xiaoxin Sheng
Thanks for the help. I ended upp with re-sorting the data so it would come in separate columns instead, il give it a new go when i have learnt some more about the tools.
Hi @Bromley,
You can use below measure to calculate diff between two date:
Diff = VAR _name = SELECTEDVALUE ( Table1[metric_name] ) VAR _currentDate = MAX ( Table1[metric_date] ) VAR _previousDate = MAXX ( FILTER ( ALLSELECTED ( Table1 ), [metric_name] = _name && [metric_date] < _currentDate ), [metric_date] ) RETURN IF ( _previousDate <> BLANK (), CALCULATE ( SUM ( Table1[metric_value] ), Table1[metric_date] = _currentDate, VALUES ( Table1[metric_name] ) ) - CALCULATE ( SUM ( Table1[metric_value] ), Table1[metric_date] = _previousDate, VALUES ( Table1[metric_name] ) ) )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |