Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I have a table with date, amount,code,location. It has missing months, I have created a table that has all month data. I have related this table to solve the issue of the missing date. My amount has blank values if I for a few "code" in a few months. I have fixed it by a measure = IF(SUM('Table'[ Amount])=BLANK(),SUM('Table'[Amount])+0,SUM('Table'[Amount]))
Now my requirement is to calculate the running total and if the values of any month are blank(now zero as I replace the blank with zero) then I want that it will show the last month value.
I am creating a table visual with code, amount and date(this is from the date table that is created to fill missing months)
Running Total =CALCULATE([measure],
Solved! Go to Solution.
Hi @Anonymous
Let me know if you'd like to get below desired running total:
RunningTotal = CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]<=MAX(Table1[Date])))
Hi,
Create a Calendar Table and build a relationship from the Date column of your Table to the Date column of the Calendar Table. In the Calendar Table, create 2 calculated column formulas to extract Year and Month
Year = Year(Calendar[Date])
Month Name = FORMAT(Calendar[Date],"mmmm")
Month Number = MONTH(Calendar[Date])
Click on any cell in the Month Name column and go to Sort By Column > Month Number.
To your visual, drag Year and Month from the Calendar Table.
Write these measures:
Measure = SUM('Table'[Amount])
Running Total = CALCULATE([Measure],DATESYTD(Calendar[Date],"31/12"))
Hope this helps.
Hello @Ashish_Mathur and @v-diye-msft ,
Thanks for your responses. But I have still got 0 with both the formulas from DAX. Also, I have to display the value with dates in end of month format. All of this is also done.
I have missing dates on my main table. So for that, I have made a calendar table and built a relationship with the main table.
Now, I have also fixed the blanks with zero as mentioned earlier. But the missing month value is not changing the zero to last month's value. For example let say my data for 2015 has Jan, March, Aug, Sep, Oct, Nov and Dec.
Month Amount Running Total
JAN 70 70
FEB 0 0
MARCH 140 210
APRIL 0 0
MAY 0 0
JUNE 0 0
JULY 0 0
AUG 1900 2110
SEP 30 2140
OCT 100 2240
NOV -1000 1240
DEC 3000 4240
Note 0 were blank in the Amount column, I have make them zero. But when I am doing running total with these missing dates then the missing month value is always 0 and not getting into account the last month value
Hi,
My solution should work. Share the link from where i can download your PBI file.
Hi, @Ashish_Mathur
For missing dates it is showing zero as the category/code column will be blank for the missing dates thus it is not calculating the running total according to the code.
I can't share the file. For example, I have created the date table to get all the month-end value. Showing the sample for 1 activity/code below.I have around 10 codes. Hope I am clear with my requirement.
Code | Date | Value | Running total | Desired |
A | 1/31/2015 | 10 | 10 | 10 |
2/28/2015 | 0 | 10 | ||
A | 3/31/2015 | 40 | 50 | 50 |
4/30/2015 | 0 | 50 | ||
5/31/2015 | 0 | 50 | ||
6/30/2015 | 0 | 50 | ||
A | 7/31/2015 | 80 | 130 | 130 |
8/31/2015 | 0 | 130 | ||
A | 9/30/2015 | 990 | 1120 | 1120 |
A | 10/31/2015 | -1000 | 120 | 120 |
A | 11/30/2015 | -200 | -80 | -80 |
A | 12/31/2015 | 2000 | 1920 | 1920 |
Hi @Anonymous
Let me know if you'd like to get below desired running total:
RunningTotal = CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]<=MAX(Table1[Date])))
Hi,
You have shared the desired output. Now share some dummy data (in a format that can be pasted in an MS Excel workbook) to work with.
Hi @Anonymous
If you'd like to show the blank value as the last value, there're 2 methods:
1. Using Fill down in power query:
2. Using the measure below:
Measure = var a = CALCULATE(MAX('Table'[Month]),FILTER(ALL('Table'),[Month]<MAX('Table'[Month])&&[Amount]<>BLANK()))
var b = CALCULATE(MAX('Table'[Amount]),FILTER(ALL('Table'),[Month]=a))
Return
IF(MAX('Table'[Amount])=BLANK()||MAX('Table'[Amount])=0,b,MAX('Table'[Amount])
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |