Forum Discussion

soham2000's avatar
soham2000
Regular Visitor
9 years ago

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

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 ra...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
soham2000
Regular Visitor
9 years ago

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.