cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

YTD total with a measure

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. 
ss1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @joshcomputer1,

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

Sample data:

1.PNG

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:

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
moonpie100 Frequent Visitor
Frequent Visitor

Re: YTD total with a measure

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.

create_column_1.PNG

 

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]

 

create_column_2.PNG

 

 

The table now has the new column.

 

create_column_3.PNG

 

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'.

create_column_4.PNG

 

create_column_5.PNG

 

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

 

Community Support Team
Community Support Team

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

Hi @joshcomputer1,

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

Sample data:

1.PNG

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:

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: YTD total with a measure

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.