Dax formula for three months rolling total

[Bijoy]

Hi All,

I have Power BI desktop tables with 4 columns (Team, Project Status, Month, Numbers). I wanted to create a new column ("3 Month Rolling Total") in which against each month I need sum of "Numbers" of the next 3 months (eg: for Jan-18 sum of Feb-18,Mar-18 and Apr-18) and only for rows with "Pipeline" in Project Status. I have done the calculation in excel and show it below in "3 Month Rolling Total" for easy understanding.

Could anybody please help me with the DAX formula to add this column in Power BI table.

Team	Project Status	Month	Numbers	3 Month Rolling Total
T1	Committed	Jan-18	30500	0
T1	Committed	Feb-18	34450	0
T1	Committed	Mar-18	30500	0
T1	Committed	Apr-18	30500	0
T1	Committed	May-18	30500	0
T1	Committed	Jun-18	30500	0
T1	Committed	Jul-18	30500	0
T1	Committed	Aug-18	30500	0
T1	Committed	Sep-18	30500	0
T1	Committed	Oct-18	30500	0
T1	Committed	Nov-18	30500	0
T1	Committed	Dec-18	30500	0
T1	Pipeline	Jan-18	0	0
T1	Pipeline	Feb-18	0	20583
T1	Pipeline	Mar-18	0	33097
T1	Pipeline	Apr-18	0	45049
T1	Pipeline	May-18	20583	33083
T1	Pipeline	Jun-18	12514	29187
T1	Pipeline	Jul-18	11951	25854
T1	Pipeline	Aug-18	8618	25854
T1	Pipeline	Sep-18	8618	25854
T1	Pipeline	Oct-18	8618	17236
T1	Pipeline	Nov-18	8618	8618
T1	Pipeline	Dec-18	8618	0
T2	Committed	Jan-18	71193	0
T2	Committed	Feb-18	79063	0
T2	Committed	Mar-18	83263	0
T2	Committed	Apr-18	65877	0
T2	Committed	May-18	66070	0
T2	Committed	Jun-18	46600	0
T2	Committed	Jul-18	38502	0
T2	Committed	Aug-18	38502	0
T2	Committed	Sep-18	38502	0
T2	Committed	Oct-18	38502	0
T2	Committed	Nov-18	38502	0
T2	Committed	Dec-18	38522	0
T2	Pipeline	Jan-18	0	0
T2	Pipeline	Feb-18	0	32524
T2	Pipeline	Mar-18	0	63204
T2	Pipeline	Apr-18	0	90094
T2	Pipeline	May-18	32524	77910
T2	Pipeline	Jun-18	30680	64010
T2	Pipeline	Jul-18	26890	49420
T2	Pipeline	Aug-18	20340	38680
T2	Pipeline	Sep-18	16780	28567
T2	Pipeline	Oct-18	12300	16267
T2	Pipeline	Nov-18	9600	6667
T2	Pipeline	Dec-18	6667	0

[v-yuta-msft · Accepted solution]

Hi Bijoy,

Creating a calculate column using DAX like this pattern may help:

3 Month Rolling Total Column =
VAR current_month = Table1[Month]
RETURN
    CALCULATE (
        SUM ( Table1[Numbers] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Team] = EARLIER ( Table1[Team] )
                && Table1[Month] > EOMONTH ( current_month, 0 )
                && Table1[Month] <= EOMONTH ( current_month, 3 )
        ),
        FILTER ( Table1, Table1[Project Status] = "Pipeline" )
    )

Regards,

Jimmy Tao

[Anonymous]

---

@v-yuta-msft wrote:

Hi Bijoy,

Creating a calculate column using DAX like this pattern may help:

3 Month Rolling Total Column =
VAR current_month = Table1[Month]
RETURN
    CALCULATE (
        SUM ( Table1[Numbers] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Team] = EARLIER ( Table1[Team] )
                && Table1[Month] > EOMONTH ( current_month, 0 )
                && Table1[Month] <= EOMONTH ( current_month, 3 )
        ),
        FILTER ( Table1, Table1[Project Status] = "Pipeline" )
    )

Regards,

Jimmy Tao

---

I have this error:
"DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

---

---

[Bijoy]

Thanks a lot Jimmy ! 

It worked like a charm !

Thanks again for your time and effort, much appreciated !

Best Regards,

Bijoy AK

[Greg_Deckler]

See if this helps out:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499