Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dynamically calculate differences based on slicer slection

Hello, I have a table with Revenues of all months and I need to build a difference between two selected months.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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.

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

 

DateDeprt.Revenue Slicer date Deprt.Revenue 1. monthRevenue 2. monthDiff.
01.01.2024A50 Period e.g. A3514-21
01.01.2024B60 01.02.2024 B122614
01.01.2024C55 and C28684
01.01.2024D99 01.04.2024 D5203198
01.01.2024E3   E2317-6
01.02.2024A35    77346269
01.02.2024B12       
01.02.2024C2       
01.02.2024D5       
01.02.2024E23       
01.03.2024A204       
01.03.2024B65       
01.03.2024C95       
01.03.2024D115       
01.03.2024E66       
01.04.2024A14       
01.04.2024B26       
01.04.2024C86       
01.04.2024D203       
01.04.2024E17      

 

ahadkarimi's avatar
ahadkarimi
Solution Specialist
2 years ago

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.