Dynamic YTD across multiple years

[Kt1097]

Hello,

after hours of reading different posts and trying out formulas, I still haven't found a solution for my problem.

There are two columns from my table that are relevant here: Sales units and date. To visualize it, I have created an Excel example table below (this is not my data source, only for visualization!).

In Power BI, I would like to display the sum of year-to-date sales units that change dynamically depending on the current date. For example: It's November 1st, the visual should show the sum of sales units from 2015, 2016, 2017, 2018, 2019 and 2020 that have been made then until this day (or the end of october). Right now, I would have to exclude the months november and december manually via filters.

Also, using YTD total Quick measure, the following error will occur although I have selected the date hierarchy/primary date column.

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");

I have already tried to delete this error line but it doesn't work anyway.

Can anyone help? 

[BI_Maverick]

Hi @Kt1097 , you can try the measure below and it would get you the desired results, thanks

YTD Sales Until Today =
VAR TodayDay = DAY(TODAY())
VAR TodayMonth = MONTH(TODAY())

RETURN
CALCULATE(
SUM('SalesTable'[Sales units]),
FILTER(
ALL('Calendar'),
'Calendar'[Month] < TodayMonth
|| (
'Calendar'[Month] = TodayMonth
&& 'Calendar'[Day] <= TodayDay
)
)
)

[Kt1097 · Accepted solution]

We finally came up with a measure that works for our purpose:

YTD sales units =

TOTALYTD(SUM('Sales'[Sales units]); 'Sales'[Date];MONTH('Sales'[Date])<=MONTH(NOW())

[Fowmy]

@Kt1097 

Create the following measure, it will show you the total ytd from the available starting date in your table

YTD Sales = 
IF(
    MAX(Sales[Date]) > EOMONTH(TODAY(),-1),
    BLANK(), 
    CALCULATE(
    SUM(Sales[Sales Units]),
        FILTER(
            ALLSELECTED(Sales[Date]),
            Sales[Date] <= MAX(Sales[Date])
        )  
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Kt1097]

Unfortunately, it doesn't work either. It just displays all sales from the available starting date until now but not the sales made in the last years until november.

[Anonymous]

Hi @Kt1097,

Fowmy 's formula looks well, I'm not so sure why they do not work on your side. Can you please share a pbix file with some dummy data and expected results to help us clarify your scenario and test coding formula?

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[amitchandak]

@Kt1097 , Try a YTD like this

example

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Kt1097]

Thanks for your answer. Still, is there a possibility to do it without the date calendar table? I already have a lot of different tables in my file...