Forum Discussion

jbenson's avatar
jbenson
Icon for Helper III rankHelper III
5 years ago
Solved

Comparing MTD amount to YTD Goal

how do i find a value to show how many tons of product sold to my ytd goal of total tons sold. 

Need Help
Show and Tell
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi jbenson ,

     

    Try the following formula:

     

    YTD Sales = 
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[date].[MonthNo] <= MONTH(TODAY())
    )
)
    GOAL Sales = 
CALCULATE(
    SUM('Table'[Goal]),
    DATESYTD('Table'[date])
)
    YTD/GOAL = [YTD Sales]/[GOAL Sales]
    monthly tons = 
DIVIDE(
    [GOAL Sales] - [YTD Sales], 
    12 - MONTH( TODAY() )
)

     

    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.

3 Replies

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

    jbenson ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    You can create both tables with date table and created mtd and ytd

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

     

    MTD Sales = CALCULATE(SUM(Goal[]),DATESMTD('Date'[Date]))

     

     

    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.

     

    refer if needed

    Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290

     

    Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400

     

     

    • jbenson's avatar
      jbenson
      Icon for Helper III rankHelper III
      5 years ago

      so my yearly goal is 1800 tons. my YTD so far is 1085. so for one of my values i took my ytd tons divided by my goal and got 60.3%. now i want to find out of my monthly tons how much more i need to sell based off the months remaining 

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi jbenson ,

         

        Try the following formula:

         

        YTD Sales = 
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[date].[MonthNo] <= MONTH(TODAY())
    )
)
        GOAL Sales = 
CALCULATE(
    SUM('Table'[Goal]),
    DATESYTD('Table'[date])
)
        YTD/GOAL = [YTD Sales]/[GOAL Sales]
        monthly tons = 
DIVIDE(
    [GOAL Sales] - [YTD Sales], 
    12 - MONTH( TODAY() )
)

         

        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.