year to date plus 3 month future year sales target data

[Learn001]

hello,

i need help i have 3 column of data: date whole yearof 2023,2024 and some of data 2025, and another of actual sales and the target sales data,

have the Year Slicer and a line and cluster column chart,

have dax to show the actual sales data using: Sales YtD = calculate(sum('SALE'),year('DATE'))

and how can i do for TARGET to show Year to Date data plus 3 month of future year? so in the chart i have data of a whole year to date 'TARGET' and 'SALE' data and see +3 month of 'TARGET' data? of course 'SALE' still blank for this future 3 month.

like this if in excel:

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution rit_ty7  offered, and i want to offer some more information for user to refer to.

hello @Learn001 , you can refer to the following solution.

Sample data 

Create a calendar table.

Calendar = CALENDAR(DATE(2023,1,1),DATE(2025,12,31))

(there is no relationship between the tables)

Then create the following measures.

Volume YtD =
IF (
    ISFILTERED ( 'Calendar'[Date].[Year] ),
    CALCULATE (
        SUM ( PROD_CM_AP25[VOLPROD] ),
        YEAR ( PROD_CM_AP25[DATE] ) = MAX ( 'Calendar'[Date].[Year] )
    ),
    CALCULATE ( SUM ( PROD_CM_AP25[VOLPROD] ) )
)

Sale Target YtD =
VAR a =
    EOMONTH ( MAXX ( ALLSELECTED ( 'Calendar' ), [Date] ), +3 )
VAR b =
    MINX ( ALLSELECTED ( 'Calendar' ), [Date] )
RETURN
    CALCULATE (
        SUM ( PROD_CM_AP25[VOL SALES TARGET] ),
        FILTER ( PROD_CM_AP25, [DATE] >= b && [DATE] <= a )
    )

Then create a visual, and put the measures and the date field of the PROD_CM_AP25 to the visual.

Create a slicer and put the date of the calendar table to the slicer.

Output

Best Regards!

Yolo Zhu

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

[Learn001]

@v-xinruzhu-msft ,thank you, thank you very much, its work like a charm 😊

[ryan_mayu]

could you pls provide the sample data that generate the chart you provided?

[Learn001]

@ryan_mayu this is the data that been use:

we record in year and month for the report with format "mm/dd/yyyy'. day will always in 1st date.

[ryan_mayu]

how you get the data for 2025?

[rit_ty7]

Hi @Learn001 ,

you should create a measure for total sales and then recall this measure in the place of sum(PROD_CM_AP25[Vol Sales Target])
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Ritesh Kumar,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Learn001]

@rit_ty7 , thank you again, the data show but still not show the +3 future month, and missing some data too

[Learn001]

value i use as following dax for above report

Volume YtD = CALCULATE(SUM(PROD_CM_AP25[VOLPROD]),YEAR(PROD_CM_AP25[DATE]))

Volume LY = CALCULATE(SUM(PROD_CM_AP25[VOLPROD]),SAMEPERIODLASTYEAR(PROD_CM_AP25[DATE].[Date]))

Sale Target YtD =

VAR A = EDATE(MAX(PROD_CM_AP25[DATE]), 3)

VAR B = DATE(YEAR(A), 4, 1)

RETURN

CALCULATE(SUM(PROD_CM_AP25[VOL SALES TARGET]),

FILTER(ALL(PROD_CM_AP25[DATE]),PROD_CM_AP25[DATE] >= B && PROD_CM_AP25[DATE] <= A))

[rit_ty7]

Hello @Learn001 ,
you can try the below dax for calculating YTD plus 3 months,
YTD_3m =

var a = EDATE(MAX(DateTable[Date]), 3)

var b = DATE(YEAR(a), 4, 1)
RETURN
CALCULATE([Sale], FILTER(ALL('DateTable'[Date]), 'DateTable'[Date] >= b && 'DateTable'[Date] <= a))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Ritesh Kumar,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Learn001]

@rit_ty7 thank you very much for quick response, i have try but still show some eror, did i miss something?

[rit_ty7]

Hello @Learn001 ,
close the sum bracket before filter expression
Sale Target YtD =
VAR A = EDATE(MAX(PROD_CM_AP25[DATE]), 3)
VAR B = DATE(YEAR(A), 4, 1)
RETURN
CALCULATE(
SUM(PROD_CM_AP25[VOL SALES TARGET]),
FILTER(
ALL(PROD_CM_AP25[DATE]),
PROD_CM_AP25[DATE] >= B &&
PROD_CM_AP25[DATE] <= A
)
)