PowerBI Comparison between dynamic daterange.

[Jaaagz]

[FONT=&quot]Hi Guys,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have "SalesTable" and If I select March 1st 2017 to March 15th 2017, it has to calculate immediate 15 days before data and show the % change. or If a user select 10 days of data it has to compare the immediate 10 days previous data and show the % change.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Please help![/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thanks & regards,[/FONT]
[FONT=&quot]Jaagz[/FONT]

 

[Ozeroth]

Hi Jaagz,

Ensure you have a standard 'Date' table with contiguous dates related to the Date column in your SalesTable.
I will assume you have a measure like [Sales Amount] already.

Then define

Sales Amount Previous = 
VAR DateRange = COUNTROWS ( 'Date' ) // Assuming contiguous selection
RETURN
CALCULATE (
    [Sales Amount],
    DATEADD ( 'Date'[Date], - DateRange, DAY )
)

Sales Amount % Change = 
DIVIDE ( [Sales Amount], [Sales Amount Previous] ) - 1

[Sales Amount Previous] just shifts the date selection backwards by the number of dates selected.

Regards,
Owen

 

[Jaaagz]

Hi, Thank you so much. The code is working fine.

I have one more query, I have 2 columns in the table called "Time on Site" = 4.22 format means 4 mins 22 sec and Page View per session = 2.50 (on an average 2.5 pages viewed).

Could you please share the code for the same to display previous period and % difference.

Please help.

Thanks & regards,
Jagadish

Hi Jaagz,

Ensure you have a standard 'Date' table with contiguous dates related to the Date column in your SalesTable.
I will assume you have a measure like [Sales Amount] already.

Then define

Sales Amount Previous = 
VAR DateRange = COUNTROWS ( 'Date' ) // Assuming contiguous selection
RETURN
CALCULATE (
    [Sales Amount],
    DATEADD ( 'Date'[Date], - DateRange, DAY )
)

Sales Amount % Change = 
DIVIDE ( [Sales Amount], [Sales Amount Previous] ) - 1

[Sales Amount Previous] just shifts the date selection backwards by the number of dates selected.

Regards,
Owen