Comparing MTD amount to YTD Goal

[jbenson]

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

[amitchandak]

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

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

[jbenson]

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 · Accepted solution]

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.