YTD and YTG for Sales and Forecast

[mb0307]

Hi,

I have two tables, ACTUAL SALES and FORECAST.

Actuals sales table only show the sales data upto July in this example.  Month of July is a curent month and not completed yet::

 

Forecast table has historic and future data::

So, I would like to create two measures YTD (actuals) and YTG (forecast).

YTD:  If Actuals Max Month-Year <> End of month then only sum previous months - example for Actuals table above YTD should only include Jan to Jun of 2020.

YTG:  If Actuals Max Date-Month <> End of month then use forecast data for that and future months - example for Forecat table above YTG should only include Jul to Dec of 2020.

is it possbile create YTD and YTG based on above critiria please?

Thanks 

[v-jayw-msft]

Hi @mb0307 ,

Check this measure.

Measure =
IF (
    FORMAT ( SELECTEDVALUE ( Actuals[yearmonth] ), "YYYYMM" )
        < FORMAT ( TODAY (), "YYYYMM" ),
    CALCULATE (
        SUM ( Actuals[actual sales] ),
        FILTER (
            ALL ( Actuals ),
            FORMAT ( Actuals[yearmonth], "YYYYMM" )
                < FORMAT ( MAX ( Actuals[yearmonth] ), "YYYYMM" )
        )
    ),
    CALCULATE (
        SUM ( Forecast[actual sales] ),
        FILTER (
            ALL ( Forecast ),
            FORMAT ( Forecast[yearmonth], "YYYYMM" ) >= FORMAT ( TODAY (), "YYYYMM" )
                && FORMAT ( Forecast[yearmonth], "YYYYMM" )
                    <= FORMAT ( MAX ( Actuals[yearmonth] ), "YYYYMM" )
        )
    )
)

Best Regards,

Jay

[amitchandak · Accepted solution]

@mb0307 , Not cleat, but seem like you need a measure like

Measure = if( eomonth(date[date],0) <=eomonth(today(),-1), sum(Sales[sales]), sum(Target[Target]))

then you can use ytd on it

Refer: https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a