Dynamically calculate differences based on slicer slection

[Alam69]

Hello, I have a table with Revenues of all months and I need to build a difference between two selected months.

[v-kaiyue-msft · Accepted solution]

Hi @Alam69 ,

Thanks for the reply from @ahadkarimi , please allow me to provide another insight: 

You can create measure.

Diff = 
VAR _min =
    CALCULATE ( MIN ( 'financials'[Date] ), ALLSELECTED ( financials[Date] ) )
VAR _max =
    CALCULATE ( MAX ( 'financials'[Date] ), ALLSELECTED ( financials[Date] ) )
VAR _s1 =
    CALCULATE ( SUM ( 'financials'[ Sales] ), 'financials'[Date] = _min )
VAR _s2 =
    CALCULATE ( SUM ( financials[ Sales] ), 'financials'[Date] = _max )
RETURN
    _s2 - _s1

It is worth noting that if three and more dates are selected in the slicer, it still calculates the difference between the maximum and minimum dates.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[v-kaiyue-msft · Accepted solution]

Hi @Alam69 ,

You can create measure.

Revenue 1. month = 
VAR _min =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )
VAR _sum =
    CALCULATE ( SUM ( 'Table'[Revenue] ), 'Table'[Date] = _min )
RETURN
    _sum

Revenue 2. month = 
VAR _max =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )
VAR _sum =
    CALCULATE ( SUM ( 'Table'[Revenue] ), 'Table'[Date] = _max )
RETURN
    _sum

Diff = 
[Revenue 2. month] - [Revenue 1. month]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Alam69]

Hello, thanks again. The two months/dates will be selected in the slicer by using the control key. So it doesn't work like you presented. Notice that my data table is joined/linked with the calendar date table.

[Alam69]

Thank you very much. I will try to make the difference for the whole year 2024. Chokran

[v-kaiyue-msft · Accepted solution]

Hi @Alam69 ,

Thanks for the reply from @ahadkarimi , please allow me to provide another insight: 

You can create measure.

Diff = 
VAR _min =
    CALCULATE ( MIN ( 'financials'[Date] ), ALLSELECTED ( financials[Date] ) )
VAR _max =
    CALCULATE ( MAX ( 'financials'[Date] ), ALLSELECTED ( financials[Date] ) )
VAR _s1 =
    CALCULATE ( SUM ( 'financials'[ Sales] ), 'financials'[Date] = _min )
VAR _s2 =
    CALCULATE ( SUM ( financials[ Sales] ), 'financials'[Date] = _max )
RETURN
    _s2 - _s1

It is worth noting that if three and more dates are selected in the slicer, it still calculates the difference between the maximum and minimum dates.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ahadkarimi]

Hey Alam, I suppose this is your table. Let me know if there is any problem:

Month	Revenue
January	10000
February	12000
March	15000
April	13000
May	16000

Create a measure to capture the revenue for the first selected month:
SelectedMonth1Revenue =
VAR SelectedMonth1 = SELECTEDVALUE('MonthlyRevenue'[Month])
RETURN
CALCULATE(SUM('MonthlyRevenue'[Revenue]), 'MonthlyRevenue'[Month] = SelectedMonth1)

Create a measure to capture the revenue for the second selected month:
SelectedMonth2Revenue =
VAR SelectedMonth2 = SELECTEDVALUE('MonthlyRevenue'[Month])
RETURN
CALCULATE(SUM('MonthlyRevenue'[Revenue]), 'MonthlyRevenue'[Month] = SelectedMonth2)

Create a measure to calculate the difference between the two selected months:
RevenueDifference =
[SelectedMonth1Revenue] - [SelectedMonth2Revenue]

[Alam69]

Hi, I adjusted my request and want to create a table in the report view like on the right.

For each month I have a table with date, deprt. and Revenue. I will import all tables via folder into Power BI.

Date	Deprt.	Revenue		Slicer date		Deprt.	Revenue 1. month	Revenue 2. month	Diff.
01.01.2024	A	50		Period e.g.		A	35	14	-21
01.01.2024	B	60		01.02.2024		B	12	26	14
01.01.2024	C	55		and		C	2	86	84
01.01.2024	D	99		01.04.2024		D	5	203	198
01.01.2024	E	3				E	23	17	-6
01.02.2024	A	35					77	346	269
01.02.2024	B	12
01.02.2024	C	2
01.02.2024	D	5
01.02.2024	E	23
01.03.2024	A	204
01.03.2024	B	65
01.03.2024	C	95
01.03.2024	D	115
01.03.2024	E	66
01.04.2024	A	14
01.04.2024	B	26
01.04.2024	C	86
01.04.2024	D	203
01.04.2024	E	17

[ahadkarimi]

Hey Alam,

You need to create 3 new measures:

First up, head to Modeling -> New Measure and create this:

Revenue 1. month =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
'Table',
'Table'[Date] = SELECTEDVALUE('SlicerTable'[Date1])
)
)

Next, go to Modeling -> New Measure again and add this:

Revenue 2. month =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
'Table',
'Table'[Date] = SELECTEDVALUE('SlicerTable'[Date2])
)
)

Then, go to Modeling -> New Measure again and add this:

Diff. = [Revenue 2. month] - [Revenue 1. month]

Lastly, add a slicer and include the Date field in it.