Dynamic previous year YTD on year selection & previous all year growth sales

[mohittimpus]

I have excel file. I need 2 Measure.
1) Dynamic previous year and all Previous year growth sales on filter year.

2) I need to add january month data with february month then february month to march month till Novermber month to December month. using measure or column.

*Note: below image has 2 red boxes

1) Year growth by month (Which I want Dynamic Measure with year filter)

2) january month data with february month then february month to march month till Novermber month to December month is calculationg from above table in same image so I want to add all month one by one to each other. (Using Measure or column.) I don't want  use second table. I want to make 2nd table using 1st table using measure.    

I am sharing a excel file :

https://drive.google.com/open?id=1CZmN47ckRu99WwVrDWh7Ul_llserMte-

I am sharing .pbix file:

https://drive.google.com/open?id=1ehNf6FRjfE8RjYaBhm9huMc7o9A75BPn

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, please take following steps:

1)Copy original [Approvals by month] as a new table for unpivot.

2)Unpivot it and add an index column.

3)Create a year slicer table:

Year SLicer = DISTINCT(SELECTCOLUMNS('Table',"Year",'Table'[Approvals by month]))

4)Create a Month number column in above copy table:

_Month = RIGHT('Table'[Month],LEN('Table'[Month])-LEN(LEFT('Table'[Month],FIND("_",'Table'[Month],1,0))))

5)Create growth by year by select year measure:

Growth = 
VAR LastYearValues =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Approvals by month]
                = MAX ( 'Table'[Approvals by month] ) - 1
                && 'Table'[_Month] = MAX ( 'Table'[_Month] )
        )
    )
VAR check =
    IF (
        MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] ),
        1,
        0
    )
RETURN
    IF (
        LastYearValues <> BLANK ()
            && check = 1,
        ( MAX ( 'Table'[Value] ) - LastYearValues ) / LastYearValues
    )

6)Create a cumulative values measure:

Cumulative =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Approvals by month] IN FILTERS ( 'Table'[Approvals by month] )
            && 'Table'[Index] <= MAX ( 'Table'[Index] )
    )
)

7)Create a cumulative growth by month by select year:

Cumulative Growth = 
VAR LastYearValues =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Approvals by month]
                = MAX ( 'Table'[Approvals by month] ) - 1
                && 'Table'[_Month] <= MAX ( 'Table'[_Month] )
        )
    )
VAR check =
    IF (
        MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] ),
        1,
        0
    )
RETURN
    IF (
        MAX ( 'Table'[Approvals by month] ) = SELECTEDVALUE ( 'Year SLicer'[Year] )
            && check = 1,
        ( [Cumulative] - LastYearValues ) / LastYearValues
    )

8)When select one year in slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak]

@mohittimpus 

This data needs some work. First of Unpivot the data and convert month 1 into the correct month. Then join with a date table and you can do analysis

https://radacad.com/pivot-and-unpivot-with-power-bi

For YTD and LYTD you can use measure like

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"))
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))

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/