Forum Discussion

JWT's avatar
JWT
Frequent Visitor
4 years ago
Solved

Last Year Year To Date summarized

Hi,

I am new to Power BI and struggling my way to find a way to calculate a total amount of sales YTD last year.

I have a table containing sales and dates (incomplete) and another table containing dates (marked as date table), which is complete. I my date table I also have a column with YTD - dates.

 

What I want is to generate a total YTD compared to YTD last year.

 
What i have this far:
 
Total Sales = SUM(Sales[Sales Price Base Exchange Total])
Sales YTD = CALCULATE(SUM(Sales[Sales Price Base Exchange Total]) , Periode[YTD])
 
Sales YTD LY = IF(HASONEVALUE(Periode[Måned]), IF(Sales[Sales YTD]<> BLANK(), CALCULATE( [Total Sales] , SAMEPERIODLASTYEAR(Periode[Dato]))) , IF(HASONEVALUE(Periode[Dato]), CALCULATE([Total Sales],DATESBETWEEN(Periode[Dato],EDATE(MIN(Periode[Dato]),-12),EOMONTH(MAX(Sales[Date]),-12)))))
 
These return: 
 

 

It works, except I want my Sales YTD LY to return a summarized amount of the values.
I have also tried the simpler TOTALYTD and SAMEPERIODLASTYEAR formulaes, but I cant get them to work!
 
 Let me know if anything is unclear - and thanks in advance
Best,
Jonatan
 
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi JWT ,

     

    Please try the following formula:

     

    Sales YTD LY =
IF (
    HASONEVALUE ( Periode[Måned] ),
    IF (
        [Sales YTD] <> BLANK (),
        CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Periode[Dato] ) )
    ),
    CALCULATE (
        [Total Sales],
        DATESBETWEEN (
            Periode[Dato],
            EDATE ( MIN ( Periode[Dato] ), -12 ),
            EOMONTH ( MAX ( Sales[Date] ), -12 )
        )
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

8 Replies

  • JWT's avatar
    JWT
    Frequent Visitor
    4 years ago

    HI amitchandak , thank you for your reply.

     

    I have written as you suggest:

     

     

    Sales YTD = CALCULATE(SUM(Sales[Sales Price Base Exchange Total]),DATESYTD(Periode[Dato], "12/31"))

     

     

     and

     

     

    Sales YTD LY = CALCULATE(SUM(Sales[Sales Price Base Exchange Total]),DATESYTD(DATEADD(Periode[Dato],-1,YEAR),"12/31"))

     

     

     

    This is now the result:

    It seems to accumulate YTD sales, but doesnt give me a total. YTD LY seems also to accumulate, but returns TY total as total LY? Also, LY data does not seem to "stop counting" for future dates.

     

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      JWT , when use YTD and you do not have a date filter, it will take the last date in the calendar to calculate YTD. So it is better to select date or range .

       

      Other wise based on today's date, with a filter

       

      YTD Today=
      var _min = date(year(today()),1,1)
      var _day = today()
      return
      CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

       

       

      LYTD =
      var _min = date(year(today())-1,1,1)
      var _max = date(year(today())-1,month(today()),day(today()))
      var _day = datediff(_min, _max,day)+1
      return
      CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))

       

      Refer, Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
      https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

      • JWT's avatar
        JWT
        Frequent Visitor
        4 years ago

        Since I need the measure to be used in other calculations, I need the filter to be within the calculation (If that makes sense).

         

        As stated, I also have a date column only returning dates for dates in this year until now. Can I use this in the date reference instead?

         

        For instance, this gives me the correct result for YTD:

         

         

        Sales YTD = CALCULATE(SUM(Sales[Sales Price Base Exchange Total]),DATESYTD(Periode[YTD], "12/31"))

         

         

        but it doesnt work for LYTD:

         

         

        LYTD = CALCULATE(SUM(Sales[Sales Price Base Exchange Total]),DATESYTD(DATEADD(Periode[YTD],-1,YEAR),"12/31"))