Forum Discussion

Sleathbcn's avatar
Sleathbcn
Regular Visitor
1 year ago
Solved

Problem measure reaching previous year goal

Hello, I need help creating a DAX measure.

 

I have 2 measures created, "Total Sales" = current sales and "LY Sales" = previous year sales.

I have doubled the "LY Sales" measure under the "Target" name.

I need to create a measure that tells me the amount left to reach the Target.

For example. If the sales for the year 2024 were $100,000 and I am closing the month of January 2025 and my sales are $15,000,

I would like the measure to indicate ($100,000 - $15,000) = ("Target" - "Total Sales"), based on the closing date in my calendar filter, for example January 31, 2025, until you reach the goal. In this case $85,000

 

In short, what it is about is that in each month of the end of the current year, I can see what I have left until I reach the figure from the previous year, which is what I take as my Target.

Thks a lot,

 

DAX
  • Fowmy's avatar
    Fowmy
    1 year ago

    Sleathbcn 


    Target Sales  =  CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )
    Remaining Sales =  [Target Sales] - TOTALYTD ( [Sales Amount], 'Date'[Date] )

    Add Year Month on a table visual and these two measures to see the results.

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    1 year ago

    Sleathbcn 


    Target Sales  =  CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )
    Remaining Sales =  [Target Sales] - TOTALYTD ( [Sales Amount], 'Date'[Date] )

    Add Year Month on a table visual and these two measures to see the results.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Sleathbcn ,

    Thanks for Fowmy's reply!
    And Sleathbcn , Since I don't know what your data looks like, and I don't know how your existing measures [Total Sales] and [LY Sales] were created, I created sample data and these two DAXs myself to give you a reference:

    LY Sales = 
SUMX (
    FILTER ( ALL ( Sales ), YEAR ( 'Sales'[Date] ) = YEAR ( TODAY () ) - 1 ),
    'Sales'[Sales]
)
    Target = 2 * [LY Sales]
    Total Sales = 
CALCULATE(
    SUM(Sales[Sales]),
    ALL(Sales),
    'Sales'[Date] <= MAX('Sales'[Date]) && YEAR('Sales'[Date]) = YEAR(TODAY())
)
    Distance from target = 
IF(
    YEAR(MAX('Sales'[Date])) = YEAR(TODAY()),
    [Target] - [Total Sales],
    BLANK()
)

    And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.