Forum Discussion

suebayes's avatar
suebayes
Icon for Resolver I rankResolver I
6 years ago
Solved

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 ...
Need Help
  • suebayes's avatar
    suebayes
    6 years ago

    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
    );
    }

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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))
  • suebayes's avatar
    suebayes
    Icon for Resolver I rankResolver I
    6 years ago

    Thank you, I understand that 😀.  Am trying to work out how to do this in Tabular Editor

     

  • suebayes's avatar
    suebayes
    Icon for Resolver I rankResolver I
    6 years ago

    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
    );
    }

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.

      😀