Measure for YTD and YTG

[Anonymous]

Hi,

I'm analyzing history, actuals and forecast, All on the same column.

I created a calendar table to get the evolution year on year. However, I'm having trouble to get the YTD (%) showing the evolution of actuals and the evolution of the YTG (forecast). The measures that I have tried shown an error or gave me a wrong number. I can't identify what I'm doing wrong.  Can someone provide some advice of how to do this. Thank you!

[Anonymous]

HI @Anonymous,

Please share some sample data and dax formula that we can test and coding formula on it.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you @Anonymous!

Here a sample of the data:

Region	Year	Month	Sales	Day	Date
Africa	2016	1	15.055	1	1/1/2016
Africa	2016	2	15.418	1	2/1/2016
Africa	2016	3	17.748	1	3/1/2016
Africa	2016	4	18.084	1	4/1/2016
Africa	2016	5	17.524	1	5/1/2016
Africa	2016	6	17.504	1	6/1/2016
Africa	2016	7	18.508	1	7/1/2016
Africa	2016	8	19.614	1	8/1/2016
Africa	2016	9	19.156	1	9/1/2016
Africa	2016	10	18.872	1	10/1/2016
Africa	2016	11	17.275	1	11/1/2016
Africa	2016	12	18.621	1	12/1/2016
Africa	2017	1	15.808	1	1/1/2017
Africa	2017	2	14.955	1	2/1/2017
Africa	2017	3	17.393	1	3/1/2017
Africa	2017	4	17.541	1	4/1/2017
Africa	2017	5	18.4	1	5/1/2017
Africa	2017	6	17.679	1	6/1/2017
Africa	2017	7	18.138	1	7/1/2017
Africa	2017	8	19.614	1	8/1/2017
Africa	2017	9	19.539	1	9/1/2017
Africa	2017	10	18.683	1	10/1/2017
Africa	2017	11	17.103	1	11/1/2017
Africa	2017	12	18.434	1	12/1/2017
Africa	2018	1	15.334	1	1/1/2018
Africa	2018	2	14.806	1	2/1/2018
Africa	2018	3	17.914	1	3/1/2018
Africa	2018	4	17.717	1	4/1/2018
Africa	2018	5	19.136	1	5/1/2018
Africa	2018	6	18.21	1	6/1/2018
Africa	2018	7	21.333	1	7/1/2018
Africa	2018	8	22.098	1	8/1/2018
Africa	2018	9	20.492	1	9/1/2018
Africa	2018	10	18.692	1	10/1/2018
Africa	2018	11	18.452	1	11/1/2018
Africa	2018	12	19.2	1	12/1/2018
Africa	2019	1	16.412	1	1/1/2019
Africa	2019	2	16.113	1	2/1/2019
Africa	2019	3	18.666	1	3/1/2019
Africa	2019	4	18.428	1	4/1/2019
Africa	2019	5	18.233	1	5/1/2019
Africa	2019	6	18.463	1	6/1/2019
Africa	2019	7	21.879	1	7/1/2019
Africa	2019	8	23.288	1	8/1/2019

I'm trying to use the quick measurment 'Year to date Total' and I already created the date hierarchy on my calendar table. However, it keeps showing an error:

Passengers YTD =
IF(
 ISFILTERED('Date'[Date]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
 TOTALYTD(SUM('Raw Data'[Sales]), 'Date'[Date].[Date])
)

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use following measure to calculate YTD based on calendar date:

YTD =
VAR currDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Raw Data'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Raw data' ),
            'Raw data'[Date] <= currDate
                && YEAR ( 'Raw data'[Date] ) = YEAR ( CurrDate )
        )
    )

Regards,

Xiaoxin Sheng