YTD Running Total - Date Table

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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-dax

My 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

[Anonymous]

There's something I'm still not doing correctly. I've tried writing the measure as:

YTD = TOTALYTD ( SUM ([Amount]), [Date], [Ledger])

What am I missing? 

[Anonymous]

You should be able to write it like this:

YTD = TOTALYTD(
    SUM(YourTable[Amount]),
    YourTable[Date]
)  

Placed on a bar chart it will look like this:

[Anonymous]

Hi Ross,

I created a separate date table and then get the same result at you had.  Is it possible to get just months along the x-axis so that 2017 January is next to 2018 January? 

Thanks again for your help. 

[Anonymous]

You'd be better off adding a Year to your 2017 data, its already labeled as 17 so it should align it for you.

I.e. when you calculated the dates, force everything to be the same Year.

[Anonymous]

That works. I'm almost there. Is there a way to drop the year of the x-axis to just show the month? If I try to link to the date table and bring in a month column it reverts back to the individual month total not the cumulative. When I have cumulative it has 2018 after every month. 

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

Thank you. 

[Anonymous]

Hi Ross,

Are you using a measure or calculated column? I can't get the months to cumulate? 

[Anonymous]

I copied your data table into the Enter Data screen so i could work with the same baseline.  So your "Date" column should be a calculated column.

As for the YTD, this is a measure.

[Anonymous]

Hi Ross, 

I still dont' understand why my data isn't cumulating by month. Here is my data. 

What am I missing? 

[Anonymous]

If I use the YTD Quick Measure I can get it to add each month on, but then I lose 2017 and can't bring that into my graph?