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

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.