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 ...
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.

     

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"))

     

     

     

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

      JWT ,what is Periode[YTD], it should be Periode[dato] , a date and Periode should be your date table marked as date table. 

      and you should Periode[dato]  in visual or any other required column from Periode

      • JWT's avatar
        JWT
        Frequent Visitor
        4 years ago

        Periode[dato] = complete date table marked as date table.

        Periode[YTD] = column with only dates for this year until now (1/1/2022 - 6/1/2022 at the moment)

         

        The matrix is only to show the values, I mainly need the total cards to be correct.