Dynamic comparison of customer status by date

[Imre]

Hi,

I'd like to create a report where I am able to categorize records and compare them between two dates or periods where dates are selected by the user. The comparison coult be any two distinct dates or any two periods where periods have the same length. Eg. This week vs last week, last quarter vs the same quarter last year, etc. 

Here is an example:

Data table:

Matrix (just to be easier to see, not needed in the report)

Result: (table form and/or visualization)

So I need to be able to compare number of transactions client by client based on user's date or date period input. 

Is it possible to do this in Power BI?

Thanks. 

[amitchandak]

@Imre , For Qtr and year you can use time intelligence for this period vs last period

For week you need additional column in date tbale

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Then create measures  like
This Week = CALCULATE(sum('Table'[Transactions]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Transactions]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
diff = [This Week] -[Last Week]

Same as last week =countx(filter(values('Table'[customer]),[diff] =0 ),[Customer])
GT as last week =countx(filter(values('Table'[customer]),[diff] >0 ),[Customer])
LT as last week =countx(filter(values('Table'[customer]),[diff] <0 ),[Customer])

Same way create other measure for Qtr and year

then create a new table like

Table =
union(
summarize('Table','Date'[Week], "Measure","same","Count",[Same as last week] , "Less" ,[GT as last week] ,"More",[LT as last week ] )
summarize('Table','Date'[Week], "Measure","same","Count",[Same as last Year] , "Less" ,[GT as last Year] ,"More",[LT as last Year ] )
)

use this table for display

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.