Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paulien1989
Advocate II
Advocate II

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?

7 REPLIES 7
jamie_yang
Frequent Visitor

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

Eric_Zhang
Employee
Employee


@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

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

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

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

Were you able to solve this?
I would like to do the same thing you are trying to do here. 

@Paulien1989

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.