Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello All,
I need help with this scenario
I have a Table
KPI_ID | Program_ID | Date | Values |
KP1 | P1 | 01-01-2017 | 23 |
KP2 | P1 | 01-01-2018 | 25 |
KP3 | P2 | 01-01-2017 | 15 |
KP4 | P2 | 01-01-2018 | 45 |
KP5 | P2 | 01-01-2019 | 85 |
KP6 | P3 | 01-01-2017 | 65 |
KP7 | P3 | 01-01-2018 | 25 |
KP8 | P4 | 01-01-2017 | 45 |
KP9 | P4 | 01-01-2017 | 10 |
now i have to generate a line chart comparing previous year and the next year values
like this
KPI_ID | Program_ID | Date | Values | previous Date | previous Values |
KP1 | P1 | 01-01-2017 | 23 | ||
KP2 | P1 | 01-01-2018 | 25 | 01-01-2017 | 23 |
KP3 | P2 | 01-01-2017 | 15 | ||
KP4 | P2 | 01-01-2018 | 45 | 01-01-2017 | 15 |
KP5 | P2 | 01-01-2019 | 85 | 01-01-2018 | 45 |
KP6 | P3 | 01-01-2017 | 65 | ||
KP7 | P3 | 01-01-2018 | 25 | 01-01-2017 | 65 |
KP8 | P4 | 01-01-2017 | 45 | ||
KP9 | P4 | 01-01-2018 | 10 | 01-01-2017 | 45 |
Solved! Go to Solution.
Hi @Prasala583 ,
Check the formula and the result as below.
Column = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[PM_ID]),MONTH('Table'[Month])=MONTH(EARLIER('Table'[Month]))&&YEAR('Table'[Month])=YEAR(EARLIER('Table'[Month]))-1))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Prasala583 ,
Please create 2 Calculated Columns
Hello there,
Thanks for the reponds
the only issue i am getting is the SUM value
i am always getting the SUM value not the individual values
Hi @Prasala583 ,
Please check the formula as below, it should help get the value you want then put it to line chart.
Column = CALCULATE(SUM('Table'[Values]),FILTER('Table',YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))-1&&'Table'[Program_ID]=EARLIER('Table'[Program_ID])))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi thanks for the responds
but i am getting an error while implementing the exact formula
can you help me with this
i am not getting the exact values but the sum value
Hi @Prasala583 ,
Check the formula and the result as below.
Column = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[PM_ID]),MONTH('Table'[Month])=MONTH(EARLIER('Table'[Month]))&&YEAR('Table'[Month])=YEAR(EARLIER('Table'[Month]))-1))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Prasala583 ,
I see you have duplicate PROGRAM_METRIC_ID in your table in same year. So you want the value group by month and PROGRAM_METRIC_ID instead of by year and PROGRAM_METRIC_ID? What's the expect out put in your visual?
Best Regards,
Jay
Your logic for the previous date is not obvious to me but you will need to use the technique found here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |