YTD illustration by selecting spec date and period of time

[Orstenpowers]

Dear all,

I do have an extensive table with worldwide incoming order figures, each line clearly stating a calendar date (format DD.MM.YYYY).

Now I would like to have a YTD visualization that allows

   to select a specific date, e.g. March 06, 2020

   to select the number of previous years, e.g. 3 years

and then the order income by March 06 should be illustrated for years 2020, 2019, 2018 & 2017.

Does anybody have an idea how to solve? I am unaware…

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[amitchandak]

I am assuming your date column has data type date in power Bi. Create a date calendar table. YTD means from 1st jan till 6th march every year unless you change year end date. Use time intelligence.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
2nd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
3rd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2nd Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

"12/31" is end of year , you can remove or change as per need

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Orstenpowers]

Hi Amit,

Thanks for your reply!

What do you mean with "Create a date calendar table"?

[v-lid-msft]

Hi @Orstenpowers ,

We can use the Calendarauto function to create a calendar table, and then create relationship with your fact table:

Date = CALENDARAUTO()

Best regards,

[Orstenpowers]

@v-lid-msft : Is this to be created as new measure under "Modelling"? Or an additional column within my data???

[v-lid-msft]

Hi @Orstenpowers ,

'Date' is a calculated table creted by "Modeling" - "New table", after create it and build relationship with the origin table, you can use @amitchandak 's measure to get the result.

If you want to select the Previous N Years, we can create a what-if parameter:

Measure:

YTD Measure = SUMX(SELECTCOLUMNS(GENERATESERIES(0,[Previous N Years Value],1),"N",[Value]),CALCULATE(SUM('Table'[Value]),ALLSELECTED('Table'),DATEADD('Date'[Date],-[N],YEAR)))

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？

Best regards,

[Orstenpowers]

Hi @v-lid-msft ,

Thanks for your continued support!

Step 1 worked and now I do have a new table that has a relation to my main data set.

But now? What should I do with amitchandak’s measure? Is the complete content of his post ONE new measure?

Best regards,

Torsten

[Orstenpowers]

My new table looks as follows. Is this correct?

In the meantime I tried to implement @amitchandak's measures.

"YTD Sales" and "This Sales" worked out fine, but now, when implementing the measure "Last YTD Sales" I am facing the following problem:

Is it possible that the problem is caused by my "IObOL table" which is also containing a "Year"?

To "which" year does @amitchandak's measure refer to?

[Orstenpowers]

@v-lid-msft :

Looking at your desktop screenshot, I would like to see the following:
Selection of the years (current - x) to be evaluated; Your solution is good!

Selection of a specific date between January 01 - December 31

As an example, I select 3 years and February 20.

Then I would like to visualize the incoming order statistics of

January 01 - February 20, 2020 illustrating the cumulated EUR value

January 01 - February 20, 2019 illustrating the cumulated EUR value

January 01 - February 20, 2018 illustrating the cumulated EUR value

January 01 - February 20, 2017 illustrating the cumulated EUR value

Another example, if I select 3 years and March 31,

January 01 - March 31, 2020 should be blank as this date does not yet exist

January 01 - March 31, 2019 illustrating the cumulated EUR value

January 01 - March 31, 2018 illustrating the cumulated EUR value

January 01 - March 31, 2017 illustrating the cumulated EUR value

The "parameters"at which my reports are looking at are called "Amount EUR" and "Order Qty". For the moment, looking at EUR is definitely sufficient.

I hope you can understand/ follow my problem!? 😯 

[v-lid-msft]

Hi @Orstenpowers ,

Sorry for that we forgot to put some screenshot in our previous reply. Now we make a new solution as following:

1. create a "What if paramater" table as following:

2. create a Calendar table as slicer:

SelectDate = CALENDARAUTO()

a calculated table

change the type to date, the format is depends on you

Relation ship between three tables

3. we limit the slicer to this year for easy to use 

4. use the following measure with the date column of fact table:

YTD Measure =
SUMX (
    DISTINCT ( 'Table'[Date] );
    IF (
        MONTH ( 'Table'[Date] ) = MONTH ( SELECTEDVALUE ( SelectDate[Date] ) )
            && DAY ( 'Table'[Date] ) = DAY ( SELECTEDVALUE ( SelectDate[Date] ) )
            && YEAR ( SELECTEDVALUE ( 'SelectDate'[Date] ) ) - 'Previous N Years'[Previous N Years Value]
                <= YEAR ( 'Table'[Date] );
        CALCULATE (
            SUM ( 'Table'[Amount] );
            FILTER (
                ALL ( 'Table'[Date] );
                'Table'[Date] >= DATE ( YEAR ( EARLIER ( 'Table'[Date] ) ); 1; 1 )
                    && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
            )
        );
        BLANK ()
    )
)

the sum value between 2017-1-1 and 2017-2-20, the sum value between 2018-1-1 and 2018-2-20 and so on

Select last 2 years will show only 2018 - 2020

the sum value between 2019-1-1 and 2019-3-31, the sum value between 2018-1-1 and 2018-3-31 , does not contain 2020 because there is no data in 2020-3-31

if we select 2020-1-1, it will show each value of 1st January for each ear

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？

BTW, our sample file in in the end of reply.

Best regards,

[Orstenpowers]

Hi @v-lid-msft ,

First of all I would like to thank you for spending that much time on my request. Without your help I was totally lost.

Secondly, thank you for this comprehensive procedure. 🤗

I created the “Calendar date slicer” without a problem, but now, when preparing the calculated table I get the following error message that I do not understand…what did I do wrong?

Your screenshot concerning the relationships, it does not show any “relation lines”? I assume I have to link “Table/Date” and “SelectDate/Date”? How to link “Previous N Years”?

Looking forward to hearing from you I remain

[v-lid-msft]

Hi @Orstenpowers ,

If you have created a calculated table "SelectDate", it will contain a "Date" column automatically, then change the type of column to "Date", please see our third screenshot in previous date, the formula of "SelectDate" table is 

SelectDate = CALENDARAUTO()

The relationship we want to show in screenshot is that there is no relationship between tables. All the calculation will complete within measure.

Best regards,

[Orstenpowers]

@v-lid-msft 

Thanks to your continued support I am getting closer and closer, but now I am struggling with your step 3.

Where do you derive this date from? From the SelectDate/Date or from Table/Date?

I see that you chose the Advanced Filtering, but when I do so, it looks as follows and I cannot just select a year like you did.

 

A short summary for my understanding; please correct me when I am wrong.

For my report, I do need three fields only!

1. Previous N years; consisting of

   Previous N Years

   Previous N Years Value

   YTD measure

2. SelectDate; consisting of

   Date

3. IObOL new (the fields that contain the original EUR values and order dates)

But I do not need field no. 3, as the EUR data will be determined through the "YTD measure", correct?

Looking forward to hearing from you...

[v-lid-msft]

Hi @Orstenpowers ,

The field used in slicer visual is 'SelectDate'[Date], and the visual filter of this slicer use the [year] of this column, we can apply it by following steps:

We cannot understand the ”IObOL new“ field, if it is your field in your dataset, maybe you should keep it, our solution assume use number and date field in fact table, but if you need to convert exchange rate, we may need to modify our YTD measure.

Best regards,

[Orstenpowers]

@v-lid-msft , thank you for this hint and all the previous others! Now it works...

Being happy to got my problem solver, I "played" with some different dates...and found out that there is a "bug".

For example, I selected February 02, 2020 and 2 years for comparison, but I got the numbers of 2018 and 2019 only.

I assume this is caused by the fact that February 02 was a Sunday and our company did not have any order income that day.

I would think the YTD measure would need to be adjusted in a way it is cumulating all values being available up to the selected value in case this selected one does not give any return value...

Unfortunately I do not have any idea how to do this...can you help?

[Orstenpowers]

@v-lid-msft : The current YTD measure is as follows:

YTD Measure =

SUMX (

    DISTINCT ( 'IObOL new (incl K Group)'[Date] );

    IF (

        MONTH ( 'IObOL new (incl K Group)'[Date] ) = MONTH ( SELECTEDVALUE ( SelectDate[Date] ) )

            && DAY ( 'IObOL new (incl K Group)'[Date] ) = DAY ( SELECTEDVALUE ( SelectDate[Date] ) )

            && YEAR ( SELECTEDVALUE ( 'SelectDate'[Date] ) ) - 'Previous N Years'[Previous N Years Value]

                <= YEAR ( 'IObOL new (incl K Group)'[Date] );

        CALCULATE (

            SUM ( 'IObOL new (incl K Group)'[Amount EUR] );

            FILTER (

                ALL ( 'IObOL new (incl K Group)'[Date] );

                'IObOL new (incl K Group)'[Date] >= DATE ( YEAR ( EARLIER ( 'IObOL new (incl K Group)'[Date] ) ); 1; 1 )

                    && 'IObOL new (incl K Group)'[Date] <= EARLIER ( 'IObOL new (incl K Group)'[Date] )

            )

        );

        BLANK ()

    )

)

[v-lid-msft · Accepted solution]

Hi @Orstenpowers ,

Sorry for we did not consider it, we update our solution as following, 

1. create another calculated table and set the type of [Date] column as Date

ShowDate = CALENDARAUTO()

2. update measure as following:

YTD Measure =
SUMX (
    DISTINCT ( 'ShowDate'[Date] );
    IF (
        MONTH ( 'ShowDate'[Date] ) = MONTH ( SELECTEDVALUE ( SelectDate[Date] ) )
            && DAY ( 'ShowDate'[Date] ) = DAY ( SELECTEDVALUE ( SelectDate[Date] ) )
            && YEAR ( SELECTEDVALUE ( 'SelectDate'[Date] ) ) - 'Previous N Years'[Previous N Years Value]
                <= YEAR ( 'ShowDate'[Date] );
        CALCULATE (
            SUM ( 'IObOL new (incl K Group)'[Amount EUR] );
            FILTER (
                ALL ( 'IObOL new (incl K Group)'[Date] );
                'IObOL new (incl K Group)'[Date]
                    >= DATE ( YEAR ( EARLIER ( 'ShowDate'[Date] ) ); 1; 1 )
                    && 'IObOL new (incl K Group)'[Date] <= 'ShowDate'[Date]
            )
        );
        BLANK ()
    )
)

3. then use the 'ShowDate'[Date] as the field of table

If you have any other questions, please kindly ask here and we will try to resolve it.

Best regards,

[Orstenpowers]

@v-lid-msft : I made some tests this weekend and it seems to work perfectly. Right now I would say that you solved my problem.

Many thanks for this! Without your on-going support I would never ever had made it. THANK YOU!

[Orstenpowers]

All the measures proposed by @amitchandak are implemented now. I had to replace a "," with a ";", then all the formula worked.

Looking at my screen, it now looks as follows:

All measures are integrated as "values" to see the outcome. And now?

[amitchandak]

@Orstenpowers , Please let us know what measure you are missing , so we can help