Comparing two dynamic time span Values in Power BI

[mikelee1701]

Hello,

I am trying to get advice on how to get BPI to allow me to compare two time periods similar to this:

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

and

https://www.reddit.com/r/PowerBI/comments/8bqzon/can_i_build_a_power_bi_report_that_compares_two/

However the examples above are for sum of sales over prior sales.  I need to compare values per day from a prior time span to a more recent time span.

In the examples below made from links above, the days are offset and I cannot compare the first and last days (or whatever offset range I choose).

For example I’ll get something like this:

Graphical representation:

What I want to do is to align the days where the first day for Nov 25,2019 (xx,994,958) is compared to Nov26,2018(xx,810,236) (and  xx,191,297 to xx,081,877).

Right now the only comparison is on Nov.26, which is not what I want.

I need to be able to choose any span of time to any other span of time (it could be last year vs this year, but not necessarily).Need to able to use two slicers.  One for the previous period, and the second slicer for the second period.  Be able to compare the values for each day from the first day of the first period of the slicer to the first day of the second period of time of the second slicer and so on. If the date range is not exactly the same say first slicer is 40 days span, and the second slicer is 47 day span, then just don't compare the values of the last 7 days. 

I would very much appreciate if someone can help me out.

Thank you,

Mike

[mahoneypat · Accepted solution]

Please try this approach to get your desired functionality.

Make a duplicated Date table with no relationship to the other tables (I assume you already have a Date table connected to your fact table).

Make a 2nd slicer with the Dates from the new Date table (Date2 in the expression below).

Try this expression with your actual Table and Column names.

Slicer 2 Rating =
VAR __thisdate =
    SELECTEDVALUE ( Date1[Date] )
VAR __minslicer1 =
    CALCULATE ( MIN ( Date1[Date] ), ALLSELECTED ( Date1[Date] ) )
VAR __daysin =
    DATEDIFF ( __minslicer1, __thisdate, DAY ) + 1
VAR __minslicer2 =
    CALCULATE ( MIN ( Date2[Date] ), ALLSELECTED ( Date2[Date] ) )
VAR __prevdate = __minslicer2 + __daysin
RETURN
    CALCULATE ( [Rating], Date1[Date] = __prevdate )

Here is a pic showing the desired functionality with the above approach.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mikelee1701]

Hi Pat,

Thank you this is much closer then I ever got!

I noticed that my second date range was one day off, so I took out the "+1" on the VAR __daysin. Seems to be working fine now.

Below I get the previous and current comparisons looking great:

I am curious why is the total line not correct(your sample seems off too)?

side note:

I had some issues with my table after I tried inserting my second row of date from the date2 table (to see it like yours), I got duplicates such as below, but I dont need to see the second set of dates anyway:

perhaps it was because my fact table I had to use "sum"(I could not get it to work without) on the [ratings] column (CALCULATE(sum(ShipRateTrack[Rating]),'Date'[Date] = __prevdate)?

thanks,

Mike

[lbendlin]

For each of your slicers you need to identify the minimum visible date (ie the date in the left box) - via min(allselected).

Then you need to subtract the left min from the right min dates and use that resulting delta value as the shifter for all your computations via DateAdd().

[amitchandak]

@mikelee1701 , let the first slicer be on date connected to table assume date. You can have second slicer on date not connected

The first measure has to respond to date and it is connected to date so the simple measure will do.

try a second measure like. Move date behind.

new measure =
var _min = minx(allselected(Date),Date[Date])
var _min1 = minx(allselected(Date1),Date1[Date])
var _diff = datediff(_min1,_min,Day)

return
calculate([measure], dateadd(Date[Date],_diff,day))