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.

     

ahadkarimi's avatar
ahadkarimi
Solution Specialist
2 years ago

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

MonthRevenue
January10000
February12000
March15000
April13000
May16000


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]

 

  • 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.