Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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] )
)
)
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.
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.
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))))
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] )
)
)
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.
hi @Anonymous
Try using DAX function "SAMEPERIODLASTYEAR"
= CALCULATE(SUM(table[score]), SAMEPERIODLASTYEAR(Date[DateKey]))
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |