cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Compare selected mont_ year with previous month_year

Hi, I have a requirement here:

I have data like below:

I need the output in Power BI as:

There will be a slicer to select year. When I select Year as 18 in the slicer, it should list data only for Year 2018 where the Profit for year 2018 is compared with Profit of 2017 and added into a column. It should be seen like below:

Any Ideas?

1 ACCEPTED SOLUTION
Community Champion

@nattargekar

May be this Column

Column =
VAR Previous_Year =
CALCULATE (
SUM ( Table1[Profit] ),
ALLEXCEPT ( Table1, Table1[Product] ),
SAMEPERIODLASTYEAR ( Table1[Date] )
)
RETURN
IF ( NOT ( ISBLANK ( Previous_Year ) ), Table1[Profit] - Previous_Year )

Regards
Zubair

4 REPLIES 4
Community Champion

@nattargekar

Try this measurE

Measure  =
VAR Previous_Year =
CALCULATE (
SUM ( Table1[Profit] ),
ALLEXCEPT ( Table1, Table1[Product] ),
SAMEPERIODLASTYEAR ( Table1[Date] )
)
RETURN
SUM ( Table1[Profit] ) - Previous_Year

Regards
Zubair

Regular Visitor

Hi Zubair,

Your code works great for a Measure. Thanks for it. I actually need to create a new Column with these values, so that I can use the column for further calculations. Can you think of DAX to create new column for same requirement ???

Community Champion

@nattargekar

May be this Column

Column =
VAR Previous_Year =
CALCULATE (
SUM ( Table1[Profit] ),
ALLEXCEPT ( Table1, Table1[Product] ),
SAMEPERIODLASTYEAR ( Table1[Date] )
)
RETURN
IF ( NOT ( ISBLANK ( Previous_Year ) ), Table1[Profit] - Previous_Year )

Regards
Zubair

Regular Visitor

It's working, Thank you so much @Zubair_Muhammad !!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.