Forum Discussion
YTD Running Total - Date Table
Hi All,
I'm relatively new to Power BI and have read through the forums and guided learning but am still having difficulty solving the problem YTD.
I tried to use a date table but can't seem to create a relationship to my table that works. The data I have looks something like the table below. I've used a clustered chart to show for each month (axis), the total amount (value) for each ledger (legend).
How do I get the clustered chart to show the cumulative amount for each ledger. So on my clustered chart for February I have the comparison YTD for each ledger?
I've tried to create a date table but can't get it to work with the period numbers (I don't have actual dates in the data only the period number). Because I also have multiple ledger for each year it's difficult to add a column with YY + MM and make it unique.
Any help would be greatly appreciated.
| Ledger | Period | Amount |
| 17 Actual | 1 | 1000 |
| 17 Actual | 1 | 1500 |
| 17 Actual | 2 | 500 |
| 17 Actual | 3 | 1000 |
| 17 Actual | 3 | 700 |
| 18 Actual | 1 | 500 |
| 18 Actual | 2 | 1000 |
| 18 Actual | 2 | 500 |
| 18 Actual | 2 | 2000 |
| 18 Actual | 3 | 600 |
| 18 Budget | 1 | 1000 |
| 18 Budget | 1 | 1000 |
| 18 Budget | 2 | 1000 |
| 18 Budget | 2 | 1000 |
| 18 Budget | 3 | 1000 |
| 18 Budget | 3 | 1000 |
In the modelling tab of the menu ribbon there is an area for Data Formatting. Set the date column's data format into something preferable.
Alternatively, since you created a date table, if you have a month column, you could make use of that column. If the ordering is wrong, create a month number column in the date table and use the "Sort By Column" option (also modeling tab) whilst you have the month name column selected. Choose Month Number as the sort column.
14 Replies
What stands out to me with your data is the missing date component. This is likely where you have had some troubles. Looking at your data you have enough timing information that we could build you some dates but we will need to convert that data into something in the Date format.
For example, if you had monthly data you would simply convert that into dates like the 1st of each month. If you have quarterly data, you convert that into the 1st of each quarter.
In your data you have <Year> <Type> and then you have the Period. How long is a period? Is a period a quarter? If so, create a column called Period Date and make that date the come from the <Year> information and then choose a month based on the number in the Period slot.
Hi Ross,
Thank you for your help. I've added a date column to my data, but I still can't work out how to get the YTD cumulative by month for each ledger.
The periods are months, so now my data looks like this. I'm trying to add a measure that I can drag into a cluster chart so show the cumulative YTD each month with month on the x-axis, with a column for each ledger.
Ledger
Period Amount Date 17 Actual 1 1000 1/01/2017 17 Actual 1 1500 1/01/2017 17 Actual 2 500 1/02/2017 17 Actual 3 1000 1/03/2017 17 Actual 3 700 1/03/2017 18 Actual 1 500 1/01/2018 18 Actual 2 1000 1/02/2018 18 Actual 2 500 1/02/2018 18 Actual 2 2000 1/02/2018 18 Actual 3 600 1/03/2018 18 Budget 1 1000 1/01/2018 18 Budget 1 1000 1/01/2018 18 Budget 2 1000 1/02/2018 18 Budget 2 1000 1/02/2018 18 Budget 3 1000 1/03/2018 18 Budget 3 1000 1/03/2018 Great! We are getting closer. Next we want to make use of the inbuilt time intelligence functions within Power BI
https://msdn.microsoft.com/en-us/query-bi/dax/time-intelligence-functions-daxMy expectation is that this formula is the one you want to start with:
https://msdn.microsoft.com/en-us/query-bi/dax/totalytd-function-dax