Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

year to date in query editor

Hello ,  I want to calculate the sum of the budget from the start of this year(from 2018:01:01)  to the last day of the previous month(2018:08:31).   But my solution gives me the total budget for the whole year.

  I have a table name  Transaction and a column named budget, a date column named Datum which contains YY:MM: DD 00:00:00  My solution is.

 

LastDateCurrentYear = TODAY().

  EndofCurrentMonth = EOMONTH([LastDateCurrentYear ]).

 EndofPreviousMonth = EOMONTH([EndofCurrentMonth],-1).

 FirstDayCY = Date(year(TODAY()),1,1).

     

YTDBudgetUptoPreviousMonth= Calculate([Budget],Datesbetween(Transaction[Datum],[FIrstDayCY],[EndofPreviousMonth ])).

 

   But it still gives me the sum of the budget for the whole year what I want is to get the sum of the budget year to date up to the previous month.

  [Budget] is a measure already defined as Sum(Transaction[budget).

 

 

 

Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please try this measure:

    YTDBudgetUptoPreviousMonth =
CALCULATE (
    SUM ( 'Transaction'[budget] ),
    FILTER (
        'Transaction',
        'Transaction'[Datum] >= DATE ( YEAR ( TODAY () ), 1, 1 )
            && 'Transaction'[Datum]
                <= ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 1 )
    )
)

    Best regards,

    Yuliana Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Please try this measure:

    YTDBudgetUptoPreviousMonth =
CALCULATE (
    SUM ( 'Transaction'[budget] ),
    FILTER (
        'Transaction',
        'Transaction'[Datum] >= DATE ( YEAR ( TODAY () ), 1, 1 )
            && 'Transaction'[Datum]
                <= ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 1 )
    )
)

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks! Now it works fine.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-yulgu-msft,

      How could I get Year to date values for this year and last year at the same column so that when I select the year 2017 in the year slicer I could see YTD values for 2017 and when I select 2018 I could see  YTD values for 2018? 

      YTD Last year BudgetUptoPreviousMonth =
CALCULATE (
    SUM ( 'Transaction'[budget] ),
    FILTER (
        'Transaction',
        'Transaction'[Datum] >= DATE ( YEAR ( TODAY () )-1, 1, 1 )
            && 'Transaction'[Datum]
                <= ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () )-1, 1 ) - 1 )
    )
)

       

      I can get a YTD value for last year using the above formula in a different column

      , But I want to see YTD last year even when the current year is selected in the year slicer.

       

      Thanks in advance!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-yulgu-msft

      Hello, Please I need help to fix this.

      I used the following formulas to create a measure for YTD and YTD LY

      ll YTD = CALCULATE(SUM('Transaktion'[konto]),FILTER('Transaktion',[Datum]>=DATE(YEAR(TODAY()),1,1) && [Datum] <=(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)))

       

      ll YTD  LY= CALCULATE(SUM('Transaktion'[konto]),FILTER('Transaktion',[Datum]>=DATE(YEAR(TODAY())-1,1,1) && [Datum] <=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)-1)))

       

      when I choose the year 2018 in the year slicer I also want to see YTD LY(YTD value for 2017), but it only shows me YTD(for 2018)