Calculate Change and % Change based on Slicer Selection

[JS00]

Hello,

I have a sales table and a date slicer. 

I want to be able to let user choose two dates from the slicer and the Change and % Change column would calculate base on what the users select. Can anyone give me some pointers?

Thank you. 

[samratpbi · Accepted solution]

Hi, what I have done is, created below sample data first.

Then created a slicer on date column.

Then created below measures:

Sales Max selected date =

CALCULATE(

    SUM(DatePoint[Sales]),

    DatePoint[Sales Date] = MAX((DatePoint[Sales Date]))

)

Sales Min selected date =

CALCULATE(

    SUM(DatePoint[Sales]),

    DatePoint[Sales Date] = MIN((DatePoint[Sales Date]))

)

above measures will give me values for max and min selected dates. If no dates selected, then max and min values from overall dates.

Now create a difference measure:

Sales Difference = ([Sales Max selected date] -[Sales Min selected date]) / [Sales Min selected date]

Now put everything in a matrix:

Now obviously column headers are not showing dates. Hence created 2 measures for max and min date and just place on top of max and min columns.

Max Selected Date = MAX(DatePoint[Sales Date])

Min Selected Date = MIN(DatePoint[Sales Date])

Hope this helps. If it resolves your problem then mark it as Solution, thanks

[Ashish_Mathur · Accepted solution]

Hi,

Here's my approach

1. Create a Calendar Table
2. Build a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Calendar Table
3. To your slicer, drag Date from the Calendar Table
4. Write these measures

Sales = sum(Data[sale])

FD = min(Calendar[date])

LD = max(Calendar[date])

Sale on FD = calculate([Sales],datesbetween(Calendar[date],[FD],[FD]))

Sale on LD = calculate([Sales],datesbetween(Calendar[date],[LD],[LD]))

Change = [Sale on LD]-[Sale on FD]

Hope this helps.

[samratpbi · Accepted solution]

Hi, what I have done is, created below sample data first.

Then created a slicer on date column.

Then created below measures:

Sales Max selected date =

CALCULATE(

    SUM(DatePoint[Sales]),

    DatePoint[Sales Date] = MAX((DatePoint[Sales Date]))

)

Sales Min selected date =

CALCULATE(

    SUM(DatePoint[Sales]),

    DatePoint[Sales Date] = MIN((DatePoint[Sales Date]))

)

above measures will give me values for max and min selected dates. If no dates selected, then max and min values from overall dates.

Now create a difference measure:

Sales Difference = ([Sales Max selected date] -[Sales Min selected date]) / [Sales Min selected date]

Now put everything in a matrix:

Now obviously column headers are not showing dates. Hence created 2 measures for max and min date and just place on top of max and min columns.

Max Selected Date = MAX(DatePoint[Sales Date])

Min Selected Date = MIN(DatePoint[Sales Date])

Hope this helps. If it resolves your problem then mark it as Solution, thanks

[JS00]

Thanks, the solution works. How did you get the column headers to show? I moved the measure Max Selected Date on top of the max column and it becomes a column and not the header. 

[samratpbi]

great to know it worked! you can create card visual and put max and min date into card visuals and put on top of respective column headers