Forum Discussion
Compare data to same previous period
I'm working to visualize Google Analytics data in both Google Data Studio and Power BI. In Google Data Studio you can add an automatic comparison to the same previous period in a scorecard. Is there a way to do this with Power BI and DAX? I'm not looking to compare with same time last month or year. But for instance, if the date slicer is set to the last 30 days, I want a scorecard to say that the number has risen or dropped compared to the 30 days before the selected period. Does anyone have a solution or best practice on this matter?
8 Replies
Paulien1989 wrote:
I'm working to visualize Google Analytics data in both Google Data Studio and Power BI. In Google Data Studio you can add an automatic comparison to the same previous period in a scorecard. Is there a way to do this with Power BI and DAX? I'm not looking to compare with same time last month or year. But for instance, if the date slicer is set to the last 30 days, I want a scorecard to say that the number has risen or dropped compared to the 30 days before the selected period. Does anyone have a solution or best practice on this matter?
You can surely do that in Power BI. For instance, in below sample, create two measures and put them in a KPI visual. Check a demo in the attached zip file.
selected period value = SUM('Table'[value])
3 days before selectd period =
VAR minSelectedDate =
MIN ( 'Table'[date] )
VAR startDate = minSelectedDate - 3
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] >= startDate
&& 'Table'[date] < minSelectedDate
)
)That's not what I'm looking for.
If I do that and I change the date slicer to cover a month, it's going to compare a month to only three days before the selected start date. I'm looking for a solution that will automatically cover the same previous period every time I change the data slicer. So when I set it to a month, I want an automatic comparison to the month before. If I set it to 15 days, I want an automatic comparison to the 15 days before that. If I set the date slicer to 188 random days, I want an automatic comparison to the 188 days preceedingly.
(But yes, good thinking it should be with the KPI visual, thanks).
Then change the second measure a little bit.
3 days before selectd period = VAR minSelectedDate = MIN ( 'Table'[date] ) VAR maxSelectedDate = MAX ( 'Table'[date] ) VAR periodDays = INT ( maxSelectedDate - minSelectedDate ) VAR startDate = minSelectedDate - periodDays RETURN CALCULATE ( SUM ( 'Table'[value] ), FILTER ( ALL ( 'Table' ), 'Table'[date] >= startDate && 'Table'[date] < minSelectedDate ) )
Paulien1989 I am running into the same problem. I think the following instruction would guide you to create such a dynamic calculation in Power BI.
http://radacad.com/previous-dynamic-period-dax-calculation
However, I think Power BI should provide this feature in the date slicer. If I have multiple measures that I want to compare, then I have to create a previous period measure for each of them. That's too much work, right?
Here is the idea that you can vote:
The MIN(date) - periodDays pattern in the accepted solution is the right approach for dynamic previous period. One important fix to the formula the periodDays calculation:
Previous Period =
VAR _minDate = MIN('Table'[date])
VAR _maxDate = MAX('Table'[date])
VAR _periodDays = INT(_maxDate - _minDate) + 1 -- +1 to include both endpoints
VAR _prevStart = _minDate - _periodDays
VAR _prevEnd = _minDate - 1
RETURN
CALCULATE(
SUM('Table'[value]),
FILTER(
ALL('Table'),
'Table'[date] >= _prevStart &&
'Table'[date] <= _prevEnd
)
)This ensures the previous period is exactly the same length as the selected period, including both start and end dates.
For anyone wanting this "compare to previous period" behavior in a table visual where end-users pick the comparison period themselves (not auto-calculated from slicer), Flexa Tables on AppSource lets users select any two periods to compare directly in the published report no DAX required
