Last Year Year To Date summarized

[JWT]

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

[JWT]

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]

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

[JWT]

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]

@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]

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.

[v-kkf-msft · Accepted solution]

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]

Hi Winniz

Thank you for your reply. Based on your file, that does indeed look like a good solution!

I have used an alternative, where the expression includes the filer eg:

Sales YTD LY = var _YTDLYmin = DATE(YEAR(TODAY())-1,1,1) var _YTDLYmax = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) return CALCULATE(SUM(Sales[Sales Price Base Exchange Total]),KEEPFILTERS(AND(Periode[Dato]>=_YTDLYmin,Periode[Dato]<=_YTDLYmax)))

Thank you for you effort.

/Jonatan

[amitchandak]

@JWT , You should try measure like these with help from date table

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

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