How to overlap lines using two different date filters and accurately display diff%

Question

It seems powerBi doesn't have the functionality to compare data from different time periods and overlap the results into one single visual. However, I came across the following article which allowed me to almost accomplish my task : https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/ After applying the method in the article (except from the normalization factor), I dragged the following values into a table visual

Date (of my sales table), Total_sales (measure), previous_sales(measure), %difference

There are currently two slicer filters (current and previous date range) applied on the table:

10/1/2018 to 10/2/2018 & 9/1/2018 to 9/2/2018

The table's output is as follows:

+-------------+----------------+----------------+-------------+
|    date     | total_sales    | previous sales | %difference |
+-------------+----------------+----------------+-------------+
| sept 1 2018 |                |      10,000    | -100.00%    |
| sept 2 2018 |                |      15,000    | -100.00%    |
| oct 1 2018  |      20,000    |                | N/A         |
| oct 2 2018  |      30,000    |                | N/A         |
+-------------+----------------+----------------+-------------+

My two issues are: how to efficiently set it up so that the %difference can accurately calculate and how can I graph the lines so they can overlap into my line visual. When I try to graph it, it gives me a line for sept. 1-2 and a long empty space before oct. 1-2.

N.B: This whole exercise is inspired by the GA comparison date feature in their visuals.

Note: Please advise if you require my measure calculations (they aren't any different from the article and so are the relationships and Date tables)

If you have the date on your axis, then of course you'll have a gap in the graph as the total_sales and previous_sales correspond to different time periods. Are you wanting the previous_sales to show up at a different date? Is that date always one month behind? — Alexis Olson
the date periods compared are always the same (5 days compared to 5 days in the past) but it could be 2 months ago 3 weeks ago (there's no logic to that). ideally the lines should overlap. — Roger Steinberg

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-11T23:08:32

Let's say we line up the two date ranges by the maximum date selected in each respective slicer (so that Sep 2 aligns with Oct 2 in your example). Define a previous_date measure as follows.

previous_date =
VAR MaxDate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR MaxPrev =
    CALCULATE (
        MAX ( 'Previous Date'[Previous Date] ),
        ALLSELECTED ( 'Previous Date'[Previous Date] )
    )
VAR CurrDate = MAX ( 'Sales'[Date] )
RETURN
    IF ( ISBLANK ( CurrDate ), BLANK (), CurrDate - ( MaxDate - MaxPrev ) )

That is, we offset the "current date" we are calculating the value for by the difference of the last date in each slicer range.

At this point, your previous_sales can simply be

previous_sales = 
VAR PrevDate = [previous_date]
RETURN CALCULATE(SUM(Sales[Sales]), Sales[Date] = PrevDate)

The result should look something like this:

How to overlap lines using two different date filters and accurately display diff%

1 Answers