Forum Discussion

cosminc's avatar
cosminc
Icon for Post Partisan rankPost Partisan
7 years ago

Rolling Average last 2 months data

Hi all,

i need to calculate a rolling average from a table but split on a dimension items

i tried this:

https://community.powerbi.com/t5/Desktop/Rolling-average-on-calculated-table/m-p/693382#M334402

but in my case the relationship Calendar - Base is inverted; on my base there are Many same Date, not unique

The calendar is marked as Date Table

 

please load as example the table form below:

 

DateYearMonthClientValueRolling AVERAGE - expected
1-Jan-201820181cosmin           90.090
1-Feb-201820182cosmin           30.060
1-Mar-201820183cosmin           30.030
1-Apr-201820184cosmin           80.055
1-May-201820185cosmin           80.080
1-Jun-201820186cosmin         100.090
1-Jul-201820187cosmin           50.075
1-Aug-201820188cosmin           60.055
1-Sep-201820189cosmin           60.060
1-Oct-2018201810cosmin           70.065
1-Nov-2018201811cosmin           60.065
1-Dec-2018201812cosmin           60.060
1-Jan-201820181marius           20.020
1-Feb-201820182marius           70.045
1-Mar-201820183marius           30.050
1-Apr-201820184marius           20.025
1-May-201820185marius           10.015
1-Jun-201820186marius           10.010
1-Jul-201820187marius           60.035
1-Aug-201820188marius           90.075
1-Sep-201820189marius           30.060
1-Oct-2018201810marius           30.030
1-Nov-2018201811marius           30.030
1-Dec-2018201812marius           20.025
1-Jan-201820181ana           20.020
1-Feb-201820182ana           10.015
1-Mar-201820183ana         100.055
1-Apr-201820184ana           20.060
1-May-201820185ana           80.050
1-Jun-201820186ana                -  40
1-Jul-201820187ana           20.010
1-Aug-201820188ana         100.060
1-Sep-201820189ana           50.075
1-Oct-2018201810ana           30.040
1-Nov-2018201811ana           90.060
1-Dec-2018201812ana           10.050

 

Thanks,

Cosmin

 

 

Need Help

1 Reply

  • cosminc's avatar
    cosminc
    Icon for Post Partisan rankPost Partisan
    7 years ago

    another solution can be something like this:

    VAR _current_date = base[Date]
    VAR _previous_date = i don't know how to obtain it - is 
    VAR _Months = { _current_date, _previous_date }
    VAR result = CALCULATE(SUM(base[Value]), ALLEXCEPT(base, base[Client]) && base[Date] IN _Months))/
    RETURN
    result
     

    i don't know how to use those 2 filters and obtain the right expression: ALLEXCEPT(base, base[Client]) && base[Date] IN _Months)

     

    tip: column date has only one value for each year month client (1st of the month)

     

    thanks,

    Cosmin