cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
viralmehta9 Frequent Visitor
Frequent Visitor

Need help on comparing records based on date slicer

Hi,

I am working on a powerBI task and I am stuck with it.
The problem statement is as follows -

The context of data is different, but I am using the analogy of a retail store and its sales unit for explaination.

I have a list of stores and its sales unit recorded each month. The data looks like this -

image.png

 

The frequency interval of dates may not be exactly 1 month. It can vary.

 

My requirement is to have a custom slicer/filter that will be a drop-down and will have the following options -
Increased, Decreased, No Change, Others. These options indicate whether the sales unit of that store increased, decreased, did not change or something else.

 

To get this filter, I am having a date filter that will be a slider where I am having the start and end date as the two end points of the slider. Date slider looks somethign like this -

image.png

 

Now below are some use case of how this should work -

Case 1:
Date slider has start date as 4/10/2019 and end date as 6/10/2019

In this date range, I want to categorize all the Stores in Increased, Decreased, No Change, Others.
This will be based on the increase/decrease of the sales unit.
For example.

On date 4/10/2019, Store A had 25 sales unit and on date 6/10/2019 Store A had 15 sales unit.

image.png
So this store will be categorized under 'Decreased'

Note here we are only checking the sales units for start and end date based on the slider.
Similarly other stores will be categorized.

 

Case 2: 

Now if I change the date slider and have the following start/end date -
Start date - 4/10/2019 End Date - 7/10/2019

Here the Store A will be categorized as 'Increased' since the
sales unit on 4/10/2019 for Store A is 25 and sales unit on 7/10/2019 for Store A is 35

image.png

So, basically when I change the date slider the stores will be categorized accordingly based on the
sales unit for their satrt/end date.


Issue in Implementation -

I have tried various tricks to see if I can compare the records based on the date selected, but I am getting no luck.
I have used to Waterfall chart to atleast show the trend of ups and downs for the stores.

I am now not able to add the Category slicer.

 

Please suggest me what approach should I follow and which diagram/chart/view will be a best fit for showing such Category.

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need help on comparing records based on date slicer

So if you create a disconnected table with your 4 different change classifications you could create a measure like the following

 

Sales = 
VAR _minDate = MIN('Date'[Date])
VAR _maxDate = MAX('Date'[Date])
VAR _minDateAmt = CALCULATE(SUM(Sales[Sales unit]), 'Date'[Date] = _minDate)
VAR _maxDateAmt = CALCULATE(SUM(Sales[Sales unit]), 'Date'[Date] = _maxDate)
VAR _eitherMinMaxIsBlank = ISBLANK(_maxDateAmt) || ISBLANK( _maxDateAmt)
VAR _selectedChange = SELECTEDVALUE(Change[Change])
VAR _result = SWITCH(_selectedChange,
"Increased", IF(_minDateAmt > _maxDateAmt && _eitherMinMaxIsBlank = FALSE(), SUM(Sales[Sales unit])) ,
"Decreased", IF(_minDateAmt < _maxDateAmt && _eitherMinMaxIsBlank = FALSE(), SUM(Sales[Sales unit])) ,
"No Change", IF(_minDateAmt == _maxDateAmt && _eitherMinMaxIsBlank = FALSE(), SUM(Sales[Sales unit])),
"Other", IF( _eitherMinMaxIsBlank = TRUE(), SUM(Sales[Sales unit])) ,
// else
SUM(Sales[Sales unit])
)
RETURN _result

It does a sum of the sales unit column over the date range, but also splits the sales into increased, decreased, tec. You can see this working in the attached pbix file

 

8 REPLIES 8
Super User
Super User

Re: Need help on comparing records based on date slicer

So if you create a disconnected table with your 4 different change classifications you could create a measure like the following

 

Sales = 
VAR _minDate = MIN('Date'[Date])
VAR _maxDate = MAX('Date'[Date])
VAR _minDateAmt = CALCULATE(SUM(Sales[Sales unit]), 'Date'[Date] = _minDate)
VAR _maxDateAmt = CALCULATE(SUM(Sales[Sales unit]), 'Date'[Date] = _maxDate)
VAR _eitherMinMaxIsBlank = ISBLANK(_maxDateAmt) || ISBLANK( _maxDateAmt)
VAR _selectedChange = SELECTEDVALUE(Change[Change])
VAR _result = SWITCH(_selectedChange,
"Increased", IF(_minDateAmt > _maxDateAmt && _eitherMinMaxIsBlank = FALSE(), SUM(Sales[Sales unit])) ,
"Decreased", IF(_minDateAmt < _maxDateAmt && _eitherMinMaxIsBlank = FALSE(), SUM(Sales[Sales unit])) ,
"No Change", IF(_minDateAmt == _maxDateAmt && _eitherMinMaxIsBlank = FALSE(), SUM(Sales[Sales unit])),
"Other", IF( _eitherMinMaxIsBlank = TRUE(), SUM(Sales[Sales unit])) ,
// else
SUM(Sales[Sales unit])
)
RETURN _result

It does a sum of the sales unit column over the date range, but also splits the sales into increased, decreased, tec. You can see this working in the attached pbix file

 

Community Support Team
Community Support Team

Re: Need help on comparing records based on date slicer

Hi @viralmehta9 ,

 

According to your description, you may need a measure to display the trend instead of a calculated column. While it is not supported to put measure into Slicer visual in Power BI Desktop.

 

As a workaround, you can create measure like DAX below to get trend, put the new measure into Card visual.

 

Trend = VAR StartDate=CALCULATE(MIN('Table (2)'[Sale Unit]),FIRSTDATE('Table (2)'[Date]))
VAR EndDate=CALCULATE(MAX('Table (2)'[Sale Unit]),LASTDATE('Table (2)'[Date]))
RETURN IF(StartDate=EndDate,"No Change",IF(StartDate>EndDate,"Decreased","Increased"))

Result:

 

25.png26.png

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support Team
Community Support Team

Re: Need help on comparing records based on date slicer

Hi  @viralmehta9 ,

 

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not,  let me know and I'll try to help you further.

 

Best regards

Amy

Super User
Super User

Re: Need help on comparing records based on date slicer

Hi,

Do you need any help with resolving this question or have you already resolved it?

viralmehta9 Frequent Visitor
Frequent Visitor

Re: Need help on comparing records based on date slicer

Hi @v-xicai . Sorry for the delay in response. The solution which you offered is actually inverse of what i was looking for. I have to actually click on individual project to see if it falls under "Increase" or "Decrease" category. Wher as my requirement was that I want to categorize the whole list of projects in each category. I appreciate you help though. Thank you so much

viralmehta9 Frequent Visitor
Frequent Visitor

Re: Need help on comparing records based on date slicer

Hi @d_gosbell , thank you for your solution. This absolutely solves my query. Though I had a question -  The dax measure uses the SelectedValue function. I did not get that part of the measure. Rest was pretty straight forward. Can you please explain what does that do? 

viralmehta9 Frequent Visitor
Frequent Visitor

Re: Need help on comparing records based on date slicer

Hi @Ashish_Mathur , the solution provided by @d_gosbell  solves my query. Thank you.

Super User
Super User

Re: Need help on comparing records based on date slicer


@viralmehta9 wrote:

The dax measure uses the SelectedValue function. I did not get that part of the measure. Rest was pretty straight forward. Can you please explain what does that do? 


So technically SELECTEDVALUE checks to see if a column has a filter applied and if that filter is for a single value, and if it is filtered in this way it returns that value.

 

But one of the most common uses is to get the current selection from a slicer. So that is all I am doing here is to get the value that has been selected in the slicer then using that in the switch statement.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 423 members 4,198 guests
Please welcome our newest community members: