Forum Discussion

stefani_vileva's avatar
stefani_vileva
Resolver II
4 years ago
Solved

Date filtered calculation

Hello everyone,

 

I have the following situation, I have Date table, Sellings, Orders and Indicatiors table. In the sellings I have all of the sales that we have made with the total revenue for each, also in the orders I have all of the orders that we have made along with how much money did we spend. The Indicators table is made based on all the distinct years that I have in the Date table. In this table I want to calculate for each year some particular values such as revenue from buying, revenue from selling and so on, bot not only for overall year but for a selected period that I have chosen using a parameter thing in PBI, in the parametars I have values for this year from 01.01.2022 until 31.12.2022 and for example I want to calculate only from 01.03.2022 until 01.04.2022. Using this selected values I want to fill the columns which I will create using some calculation. The dates from the parametars filter are calculated with the following equations:

 

 

Selected Max Date = MAXX(Datum, Datum[Datum Value])
Selected Min Date = MINX(Datum, Datum[Datum Value])

 

 

The calculation for revenue from sales is the following: 

 

 

Revenue = 

VAR curr_date_min = Date('Indicators'[Year], MONTH([Selected Min Date]), DAY([Selected Min Date]))
VAR curr_date_max = Date('Indicatiors'[Year], MONTH([Selected Max Date]), DAY([Selected Max Date]))

RETURN CALCULATE(SUM(Orders[Revenue]), Orders[Date] >= curr_date_min && Orders[Date] <= curr_date_max)

 

 

 

However, the result of the following calculation it is not correct, because always for Selected Min Date it takes the lowest value of the parametar (01.01.2022) and for the maximum (31.12.2022). Can someone explain to me what am I doing wrong?

 

Thanks a lot!

Kind regards,

Stefani

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi stefani_vileva ,

    I downloaded your file, because Revenue is a calculated column in your file, the value of a calculated column cann't change based on the slicer, they are only calculated when you first define them and during a dataset refresh. So always for Selected Min Date it takes the lowest value of the parametar (01.01.2022) and for the maximum (31.12.2022). Refer to this article for more information: Difference between calculated columns and measures? 

    Here's my solution.

    Create a measure.

    Measure =
VAR curr_date_min =
    DATE ( MAX ( 'Indicators'[Year] ), MONTH ( [Selected Min Date] ), DAY ( [Selected Min Date] ) )
VAR curr_date_max =
    DATE ( MAX ( 'Indicators'[Year] ), MONTH ( [Selected Max Date] ), DAY ( [Selected Max Date] ) )
RETURN
    CALCULATE (
        SUM ( Sellings[Revenue] ),
        Sellings[Date] >= curr_date_min
            && Sellings[Date] <= curr_date_max
    )

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

5 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi stefani_vileva ,

    I downloaded your file, because Revenue is a calculated column in your file, the value of a calculated column cann't change based on the slicer, they are only calculated when you first define them and during a dataset refresh. So always for Selected Min Date it takes the lowest value of the parametar (01.01.2022) and for the maximum (31.12.2022). Refer to this article for more information: Difference between calculated columns and measures? 

    Here's my solution.

    Create a measure.

    Measure =
VAR curr_date_min =
    DATE ( MAX ( 'Indicators'[Year] ), MONTH ( [Selected Min Date] ), DAY ( [Selected Min Date] ) )
VAR curr_date_max =
    DATE ( MAX ( 'Indicators'[Year] ), MONTH ( [Selected Max Date] ), DAY ( [Selected Max Date] ) )
RETURN
    CALCULATE (
        SUM ( Sellings[Revenue] ),
        Sellings[Date] >= curr_date_min
            && Sellings[Date] <= curr_date_max
    )

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    stefani_vileva , not very clear Try like

     


    new measure =
    var _max = maxx(allselected(Date),Date[Date])
    var _min = minx(allselected(Date),Date[Date])
    return
    calculate( sum(Table[revenue]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    or

     

    or

     

    new measure =
    var _max = max(Date[Date])
    var _min = min(Date[Date])
    return
    calculate( sum(Table[revenue]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

    • stefani_vileva's avatar
      stefani_vileva
      Resolver II
      4 years ago

      amitchandak There is a little bit confusion. The tables orders and sales are connected to date table, but I have other table - parametar called datum which is not connected to anything and only serves as a filter for selecting the period for the calculation. I did this because if I use only the date table, if I filter using the filter panel, then I can't get the information for defining the dates of previous years.

       

      If there are still any unclarities please let me know.

       

      Thanks a lot anyway but it doesn't help.