cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paulien1989 Regular Visitor
Regular Visitor

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?

6 REPLIES 6
Moderator Eric_Zhang
Moderator

Re: Compare data to same previous period


@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?


@Paulien1989

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
)
)

Capture.PNG

Paulien1989 Regular Visitor
Regular Visitor

Re: Compare data to same previous period

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).

Moderator Eric_Zhang
Moderator

Re: Compare data to same previous period

@Paulien1989

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 Regular Visitor
Regular Visitor

Re: Compare data to same previous period

Hmm it looks good, but it's not working. I want to compare the number of goal completions between the selected period and the previous period. When I use your measure, it looks like this:

 

Knipsel.PNG

Moderator Eric_Zhang
Moderator

Re: Compare data to same previous period

@Paulien1989

What if put those two measures to split card visuals? By the way, could you share a pbix file with sample data?

Highlighted
jamie_yang Frequent Visitor
Frequent Visitor

Re: Compare data to same previous period

@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:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19129159-compare-to-previous-peri...

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)