Forum Discussion

thebeginner1204's avatar
thebeginner1204
Helper I
3 years ago

Same period last year

Hello together

 

I am currently trying to apply YTD to last year.
Unfortunately, this works neither with "sameperiodlastyear" nor with a "Dateadd" filter.

I would like to display the current value (YTD) and the last year in an identical period in a bar chart.


The following formula works with a slicer:

= CALCULATE(SUM(TBL_Sales)]),SAMEPERIODLASTYEAR('Date Slicer Choice'[Date]))

 

But I want to solve this without a slicer on the dashboard.
does anyone have a tip for me? THX a lot

 

Regards,

DATEADD
Sameperiodlastyear

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    thebeginner1204 , First of all make sure 'Date Slicer Choice' is date table, marked as date table and joined with Only date of Fact Table

     

    CALCULATE(SUM(TBL_Sales[Sales]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    CALCULATE(SUM(TBL_Sales[Sales]),dateadd('Date'[Date],-1, year))

     

    for YTD , please find exmaples

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    or

    YTD =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = eomonth(_max,-1*MONTH(_max))+1
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    LYTD =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
    var _min = eomonth(_max,-1*MONTH(_max))+1
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

     

    Once you select a date it will not show trend for YTD. It will show YTD values for that date. refer this Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

     

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

     

    • thebeginner1204's avatar
      thebeginner1204
      Helper I
      3 years ago

      amitchandak Thank you for your explanation.

      Q1:

      CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

      -> What is the meaning of "[net] in this example?

      Q2: 

      PBI Desktop has an error "No single value can be determined for the column "Date" in the table "Date". This can happen when a measure formula refers to a column with many values without specifying an aggregation such as MIN, MAX, COUNT or SUM to get a single result."

       

      i have try the following syntax:

       

      CALCULATE(SUM(TBL_Sales[Betrag]),DATESBETWEEN('Date'[Date],_min,_max))
      and your Proposal
      CALCULATE([Reveneu],DATESBETWEEN('Date'[Date],_min,_max))
       
      both with the same error. what is the reason for this?
       
      thank you in advance, Regards