Forum Discussion
Tabular Editor FYTD
I am trying to create a Fiscal YTD measure in Tabular Editor but am getting stuck on the code to include a text value at the end of the YTD, I've tried to create a variable but the endYear has to be in quotes. I've underlined the part I'm stuck on.
// Creates a TOTALYTD measure for every selected measure.
var dateColumn = "'Date'[Date]";
var endYear = "31/05";
foreach(var m in Selected.Measures)
{ m.Table.AddMeasure( m.Name + " YTD", // Name "TOTALYTD(" + m.DaxObjectName + ", " + dateColumn + ", " + "'endYear'"+ ")", m.DisplayFolder // Display Folder ); }
I think I have worked it out
// Creates a TOTALYTD measure for every selected measure.
foreach(var m in Selected.Measures) {
m.Table.AddMeasure(
m.Name + " YTD", // Name
"TOTALYTD(" + m.DaxObjectName + ", 'Date'[Date], " + "\"31/05\")", // DAX expression
m.DisplayFolder // Display Folder
);
}
5 Replies
suebayes - Not entirely sure, you may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
Also, https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.suebayes , You can use year-end in datesytd
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"5/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"5/31")) This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"5/31")) Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"5/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"5/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))Thank you, I understand that 😀. Am trying to work out how to do this in Tabular Editor
I think I have worked it out
// Creates a TOTALYTD measure for every selected measure.
foreach(var m in Selected.Measures) {
m.Table.AddMeasure(
m.Name + " YTD", // Name
"TOTALYTD(" + m.DaxObjectName + ", 'Date'[Date], " + "\"31/05\")", // DAX expression
m.DisplayFolder // Display Folder
);
}Hi suebayes ,
Glad you have resolved it. You could accept your answer as solution. Others who have the same request will benefit from this thread.
😀
