cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bijoy Frequent Visitor
Frequent Visitor

Dax formula for three months rolling total

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.

 

TeamProject StatusMonthNumbers3 Month Rolling Total
T1CommittedJan-18305000
T1CommittedFeb-18344500
T1CommittedMar-18305000
T1CommittedApr-18305000
T1CommittedMay-18305000
T1CommittedJun-18305000
T1CommittedJul-18305000
T1CommittedAug-18305000
T1CommittedSep-18305000
T1CommittedOct-18305000
T1CommittedNov-18305000
T1CommittedDec-18305000
T1PipelineJan-1800
T1PipelineFeb-18020583
T1PipelineMar-18033097
T1PipelineApr-18045049
T1PipelineMay-182058333083
T1PipelineJun-181251429187
T1PipelineJul-181195125854
T1PipelineAug-18861825854
T1PipelineSep-18861825854
T1PipelineOct-18861817236
T1PipelineNov-1886188618
T1PipelineDec-1886180
T2CommittedJan-18711930
T2CommittedFeb-18790630
T2CommittedMar-18832630
T2CommittedApr-18658770
T2CommittedMay-18660700
T2CommittedJun-18466000
T2CommittedJul-18385020
T2CommittedAug-18385020
T2CommittedSep-18385020
T2CommittedOct-18385020
T2CommittedNov-18385020
T2CommittedDec-18385220
T2PipelineJan-1800
T2PipelineFeb-18032524
T2PipelineMar-18063204
T2PipelineApr-18090094
T2PipelineMay-183252477910
T2PipelineJun-183068064010
T2PipelineJul-182689049420
T2PipelineAug-182034038680
T2PipelineSep-181678028567
T2PipelineOct-181230016267
T2PipelineNov-1896006667
T2PipelineDec-1866670
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dax formula for three months rolling total

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

4 REPLIES 4
Super User
Super User

Re: Dax formula for three months rolling total

See if this helps out:

 

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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Dax formula for three months rolling total

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

Bijoy Frequent Visitor
Frequent Visitor

Re: Dax formula for three months rolling total

Thanks a lot Jimmy ! 

It worked like a charm !

Thanks again for your time and effort, much appreciated !

 

Best Regards,

Bijoy AK

Highlighted
Dom_Bal_1987 Regular Visitor
Regular Visitor

Re: Dax formula for three months rolling total


@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."