Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mohittimpus
Helper V
Helper V

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

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.    

 

task.png

 

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

 

 

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

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:

60.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

2 REPLIES 2
v-gizhi-msft
Community Support
Community Support

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:

60.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

amitchandak
Super User
Super User

@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/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.