Forum Discussion

Kt1097's avatar
Kt1097
New Member
5 years ago
Solved

Dynamic YTD across multiple years

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? 
 
Need Help
  • Kt1097's avatar
    Kt1097
    5 years ago

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

7 Replies

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

    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's avatar
      Kt1097
      New Member
      5 years ago

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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Kt1097
      New Member
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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

  • Kt1097's avatar
    Kt1097
    New Member
    5 years ago

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

  • BI_Maverick's avatar
    BI_Maverick
    Regular Visitor
    1 year ago

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