cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arundhatid04
Helper I
Helper I

i want calculated column which find last year same month score for the given goal

Hi Team,

I need urgent help.

I have one table having columns (with last 5 years data)

 

Date                Goal         score    target

20200315        goal1       1500     2000

20200315        goal2        34         35

20190315        goal1       1100     1500

20190315        goal2        26         30

 

i want to display

 

 

Goal      Target      Currentyearscore       PreviousYearscore

 

 

which should change with selection of MonthYear Slicer.

 

I can do for Target and Currentyearscore but not  PreviousYearscore.

Please guide me to have one column which is displaying the score for previous year for the given date_key.

 

Its Urgent.I am new to power bi.

 

Regards,

Arundhati

 

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @arundhatid04 ,

If you want a calculated column, you can try this:

PreviousScore =
CALCULATE (
    SUM ( 'Table'[score] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date].[Year]
            = EARLIER ( 'Table'[Date].[Year] ) - 1
            && 'Table'[Date].[MonthNo] = EARLIER ( 'Table'[Date].[MonthNo] )
            && 'Table'[Goal] = EARLIER ( 'Table'[Goal] )
    )
)

column.pngre.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User III
Super User III

Hi,

The Date column should be converted into a proper date column first.  Then create a Calendar Table with a  calculated column to extract the Year.  Create a relationship from the proper Date column of your Data Table to the Date column of the Calendar Table.  To your slicer, drag Year from the Calendar Table and select any year.  Write these mesures

Target value = sum(Data[Target])

Current Year score = sum(Data[Score])

Previous Year score = calculate([current year score],previousyear(calendar[date]))

Hope this helps. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Thank you very much its worked with one issue.It is not workinng for feb month leap year.

I have Date 29feb2021, it shows previous year score blank.

same happens if i used previous year, SAMEPERIODLASTYEAR, or DATEADD(DATEKEY,-12,Month),

SAMEPERIODLASTYEAR ( LASTDATE ( dCalendar[Date] ) ) 

Please suggest solution for this.

Regards,

Arundhati

You are welcome.  See if this works

Previous Year score = if(isblank(calculate([current year score],previousyear(calendar[date]))),calculate([current year score],datesbetween(calendar[date],eomonth(max(calendar[date]),-12),eomonth(max(calendar[date]),-12))))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yingjl
Community Support
Community Support

Hi @arundhatid04 ,

If you want a calculated column, you can try this:

PreviousScore =
CALCULATE (
    SUM ( 'Table'[score] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date].[Year]
            = EARLIER ( 'Table'[Date].[Year] ) - 1
            && 'Table'[Date].[MonthNo] = EARLIER ( 'Table'[Date].[MonthNo] )
            && 'Table'[Goal] = EARLIER ( 'Table'[Goal] )
    )
)

column.pngre.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

alekhved
Resolver II
Resolver II

hi @arundhatid04 

 

Try using DAX function "SAMEPERIODLASTYEAR"

 

= CALCULATE(SUM(table[score]), SAMEPERIODLASTYEAR(Date[DateKey]))

 

Thanks!

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors