Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ); }
Solved! Go to Solution.
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
);
}
@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))
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.
😀
Thank you, I understand that 😀. Am trying to work out how to do this in Tabular Editor
@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-TIT... 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |