YTD total with a measure

[joshcomputer1]

Hi, I used the quick measure function to get a YTD total, but it is not getting the exact right number. I don't know why and how to adjust it to work. 

I have a year.month column from my calendar.  What I want is a ytd total of the 'Monthly%' column.  The math to get that % is based on the two columns to the right of it ('termed employees' / 'total employees')

RunningTotalYTD is the quick measure:

RunningTotal YTD =
totalytd( [Monthly%],'DateTable1'[Date],
FILTER(
ALLSELECTED('DateTable1'[date]),
ISONORAFTER(('DateTable1'[date]), MAX('DateTable1'[date]), DESC))

The first month is right, but everything after that is a bit less than it should be. 

[v-danhe-msft]

Hi @joshcomputer1,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @joshcomputer1,

Based on my test, you could refer to below steps:

Sample data:

Create two measures:

Monthly% = DIVIDE(MAX('Table1'[Termed Employees]),MAX('Table1'[ToTal Employees]))

Running Total YTD = CALCULATE(SUMX('Table1','Table1'[Monthly%]),FILTER(ALL(Table1),'Table1'[Year.Month]<=MAX('Table1'[Year.Month])))

Result:

You could also download the pbix file to have a view:

https://www.dropbox.com/s/mm901axt8eevm9b/YTD%20total%20with%20a%20measure.pbix?dl=0

Regards,

Daniel He

[moonpie100]

Hi.

I went ahead and tried to something different than a quick measure and I think I believe this is what you might want.

What I ended up doing was creating a custom column using Edit Queries.

Click on Edit Queries. 

Click on the table where you want to add your column.

Click on the Add Column Tab, then click on Custom Column.

Name your column, then enter the formula by selecting the columns from the available columns list and putting the necessary division symbol between them

[Termed Employees]/[Total Employees]

The table now has the new column.

Now you want to change the data type to percent, by selecting the column, going to the Transform tab, and changing the data type to 'Percentage'.

I am not sure if this solves your problem, but I hope it helps.