Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
@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."
Thanks a lot Jimmy !
It worked like a charm !
Thanks again for your time and effort, much appreciated !
Best Regards,
Bijoy AK
See if this helps out:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |