Regarding date range values to be used for changes in the variables( such as pageviews, orders,etc.)

[soham2000]

Hello,

I am looking to take two data ranges values.

For example,

one slicer for current date range (016,9,12) - (2016,10,21) called as current_range

second slicer for previous date range (2016,7,12) - (2016,8,12) called as previous_range

The column previous range< current_range

So, in the output in the visualization layer, for an end user it will have 3 columns.

1st column- to have the value based upon the current_range

2nd column - to have the value based upon the previous_range

3rd column- delta or change in % (difference in growth or depreciation for that column)

The columns are generally orders, revenue, pageviews, etc.

So, I want to understand how can I do this stuff like in excel it can be done using slicer. ( for 1 column if i can do this date range stuff then for others I can do it too, so urgent help needed).

----------------------------------

Secondly, I want to know that in powerbi when in visualization level the values are aggregated based upon slicers. So, the total orders, or the aggregation variables work which is simply the sum.

But, if there are % columns such as the bounce rate, exit rate, etc. how do we do that? Since, right now it direct sums up or does the average so the % becomes >100 %.

But in columns which are % it should have weighted average calculations not direct average or sum.

So, looking forward to help in this too urgently?

[v-sihou-msft]

@soham2000

Your first question:

Since you don't put date field in visual for end users, you just need to create two measures.

For current total measure, you can use ALLSELECTED() to limit the filter context into the selected date range.

Current Total = CALCULATE(SUM('Table'[Amount]),ALLSELECTED('Table'[Date])) 

For previous total, you need to use DATESBETWEEN() and manually have the FIRSTDATE() and LASTDATE() of the selected dates minus 1 month. The formula can be like:

Previous Total = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    DATESBETWEEN (
        'Table'[Date],
        DATE ( YEAR ( FIRSTDATE ( ALLSELECTED ( 'Table'[Date] ) ) ), MONTH ( FIRSTDATE ( ALLSELECTED ( 'Table'[Date] ) ) ) - 1, DAY ( FIRSTDATE ( ALLSELECTED ( 'Table'[Date] ) ) ) ),
        DATE ( YEAR ( LASTDATE ( ALLSELECTED ( 'Table'[Date] ) ) ), MONTH ( LASTDATE ( ALLSELECTED ( 'Table'[Date] ) ) ) - 1, DAY ( LASTDATE ( ALLSELECTED ( 'Table'[Date] ) ) ) )
    )
)

To calculate the change rate, the formula can be like:

Change Rate = ([Current Total]-[Previous Total])/[Previous Total]

Your second question:

In Power BI, you can change the aggregation type for field in visual.

 

Regards,

[soham2000]

Hello,

Thanks for the response.

But, in the 1st question what we are looking for is any date range thats my question. Not only specifically the previous month. Something similar to this we thought of for the previous month. 

But, we want with any random comparisons. Here it is done month-1 (So, in the formula it will be static).

In short the user shall be able to choose any date range to compare.

Also, in the formula it will break down let say we are in 2018,Jan,01

So, when we do only month-1 = then the previous total= value from 2018, Dec,01

Instead of 2017,Dec,01.

Since, only the month parameter is being only decreased.

For the second question, I have tried with Don't summarize.

But the problem with Don't summarize is-

Since, we have few products, countries, dates and their corresponding values for orders, pageviews, etc.

I have sliver for product names, countries and slicer for date range.

So, if I don' summarize it will show repetition of products if a product is sold in multiple dates; so it will show that product multiple times; which we don't want.

We want the product names to be unique; also parallely not to show the total below for the % or do some weighted average.