Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Values previous Year to date

Hi All,

I am currently looking at a TotalYTD and also looking at the same date period from the year before.

Total Value YTD = TOTALYTD(Sum('Table 1'[Value]),'Table 1'[Full Date], 'Table 1'[Measure Version]="Actual")

 

This works fine and this gives me the Total year to date for the filter I require.

But the below does not seem to work;

Total Value YTD Last Year = CALCULATE(SUM('table 1'[Value]),PREVIOUSYEAR('table 1'[Full Date]))

 

I just get a blank value from this and I do not seem to get any data, this sum does not need to be filtered.

Also does this also only look at the same values say Jan – July? or will this calculate Jan – Dec?

Thanks

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Hi Griffy,

     

    Are the date value in [Full Date] column continuos? Could you share some sample data?

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Jimmy,

       

      Thanks, no the Full Date table is not continuous, this has been made up as in the data set that I have only had Month and Year, so when I import the data I create a new column called Full date which is 01+Month+year.

       

      I won't be able to do a full date set but the data is like this below but just with a lot more in it.

       

      Full DateValueMeasure Version
      01-Jan-18$2,248.95Actual
      01-Jan-18$225,267.49Actual
      01-Mar-18$3,017.19Actual
      01-Mar-18$2,988.20Actual
      01-Sep-18$1,438.50Actual
      01-Sep-18$1,776,906.64Actual
      01-Aug-17$701.00Last Year
      01-Jan-17$2,248.95Last Year
      01-Jan-17$225,267.49Last Year
      01-Mar-17$3,017.19Last Year
      01-Mar-17$2,988.20Last Year
      01-Sep-17$1,438.50Last Year
      01-Sep-17$1,776,906.64Last Year
      01-Aug-17$701.00Last Year
         

       

       

      Within the same Data set, i also have two orther measures Plan & RF.

       

      I have tried to create a date table with the below code and tried this dax"

      Total Value YTD Last Year = CALCULATE(SUM('BPC LBM'[Value]),PREVIOUSYEAR('Calendar'[Date]))"
      Calendar = 
VAR Days = CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2018, 12, 31 ) )
RETURN ADDCOLUMNS (
    Days,
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Year Month", FORMAT ( [Date], "mmm yy" )
)

      Thanks