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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

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
Super User

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/
Anonymous
Not applicable

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 @Anonymous ,

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.

Anonymous
Not applicable

hi @Anonymous 

 

Try using DAX function "SAMEPERIODLASTYEAR"

 

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

 

Thanks!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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