Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 f...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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

     

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous ,

 

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:

 

 

 

 

Best Regards,

Amy

 

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