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.

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    Not applicable
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.