Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi Team,
Need help in solving the below problem.
We need to take the running sum of future 3 month data based on the month.
Date | Data | |
1/1/2019 | 10 | |
1/5/2019 | 20 | |
1/7/2019 | 30 | |
2/2/2019 | 5 | |
2/15/2019 | 45 | |
2/25/2019 | 47 | |
3/1/2019 | 50 | |
3/6/2019 | 20 | |
3/27/2019 | 45 | |
4/4/2019 | 65 | |
4/25/2019 | 35 | |
4/29/2019 | 25 | |
5/1/2019 | 65 | |
5/10/2019 | 40 | |
6/15/2019 | 54 | |
7/25/2019 | 25 | |
8/24/2019 | 36 | |
output | Data | Comment |
Jan-19 | 337 | Running sum of Feb,march ,April |
Feb-19 | 345 | running sum of march,april,may |
Mar-19 | 284 | Running sum of April ,may ,june |
Apr-19 | 184 | Running sum of May ,June, July
|
Thanks,
Raj
Solved! Go to Solution.
You will need a Calendar table if you dont have one already. Make sure it has a Year, MonthName, MonthNumber ( to sort month name) column.
Related that to yoru main table on Date (1:M) and make sure to put columns from the Calendar tables as filters ( rows, columns, ect).
Then you can write these two measures:
Total of Data = SUM ( Table2[Data] ) Total of Next 3 Months = CALCULATE( [Total of Data], DATESBETWEEN( DimCalendar[Date], LASTDATE(DimCalendar[Date]), DATEADD( LASTDATE(DimCalendar[Date]),3,MONTH) ) )
And here's the final table:
You will need a Calendar table if you dont have one already. Make sure it has a Year, MonthName, MonthNumber ( to sort month name) column.
Related that to yoru main table on Date (1:M) and make sure to put columns from the Calendar tables as filters ( rows, columns, ect).
Then you can write these two measures:
Total of Data = SUM ( Table2[Data] ) Total of Next 3 Months = CALCULATE( [Total of Data], DATESBETWEEN( DimCalendar[Date], LASTDATE(DimCalendar[Date]), DATEADD( LASTDATE(DimCalendar[Date]),3,MONTH) ) )
And here's the final table:
Hi I used the same data and formula from above. Here is what I come up with:
I don't understand why Dec-19 has 617 as the next 3 months total. Can you please help? Thank you!
Hi Nick,,
Can you send me the pbix file.
Thanks,
Raj
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |