Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago
Solved

3 m rolling

Hi,   Thanks for looking at my post.   I got stuck with 3 months rolling formula and any help will be much appreciated.   I have data and i wanted to sum the rolling 3 months please.  In this ...
  • Anonymous's avatar
    Anonymous
    2 years ago

     

    Thanks for the reply from aduguid , please allow me to provide another insight:

     

    Hi  jimpatel ,

    Here are the steps you can follow：

    1. Create measure.

    Value =
IF(    MAX('Table'[Date])=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Date]),
    SUMX('Table',[Total]),0)
    3 m rolling =
var _enddate=EOMONTH(MAX('Table'[Date]),0)
var _startdate=EOMONTH(MAX('Table'[Date]),-3)
RETURN
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]>=_startdate&&'Table'[Date]<=_enddate),
[Value])

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this 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

 

Thanks for the reply from aduguid , please allow me to provide another insight:

 

Hi  jimpatel ,

Here are the steps you can follow：

1. Create measure.

Value =
IF(    MAX('Table'[Date])=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Date]),
    SUMX('Table',[Total]),0)
3 m rolling =
var _enddate=EOMONTH(MAX('Table'[Date]),0)
var _startdate=EOMONTH(MAX('Table'[Date]),-3)
RETURN
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]>=_startdate&&'Table'[Date]<=_enddate),
[Value])

2. Result:

 

 

Best Regards,

Liu Yang

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

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago

Sorry for reopening this post.

May i know how to change below formula please.

 

* This formula is summing data if it is falling on same day. That is i wanted to take one value or average value if it is falling on same date please.  Any help will be highly appreciated.

 

Value = IF( MAX('Table'[Date])=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Date]), SUMX('Table',[Total]),0)

 

The reason i found is , if 2 rows fall in the minimum criteria the code is adding those values. Is there anyway to not to do that pleasE?

 

 

Thanks a lot