Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Following is the data sample we are dealing with , which is having 27 months data. From which we need to extract 12 months data and show it as defined in the output dataset.
For now we are using matrix and have pivoted the data on date column, but we are not able to add the average column as per our requirement.
Data Sample-
Date | KPI-1 | KPI-2 | KPI-3 | KPI-4 | KPI-5 | KPI-6 |
Saturday, October 01, 2016 | 691 | 585 | 501 | 654 | 565 | 586 |
Tuesday, November 01, 2016 | 575 | 516 | 636 | 562 | 648 | 513 |
Thursday, December 01, 2016 | 596 | 615 | 608 | 634 | 537 | 521 |
Sunday, January 01, 2017 | 512 | 600 | 699 | 698 | 572 | 589 |
Wednesday, February 01, 2017 | 534 | 520 | 691 | 538 | 614 | 551 |
Wednesday, March 01, 2017 | 520 | 531 | 663 | 551 | 561 | 540 |
Saturday, April 01, 2017 | 636 | 683 | 627 | 524 | 509 | 514 |
Monday, May 01, 2017 | 665 | 542 | 542 | 527 | 571 | 582 |
Thursday, June 01, 2017 | 639 | 517 | 679 | 632 | 559 | 631 |
Saturday, July 01, 2017 | 675 | 665 | 528 | 680 | 653 | 625 |
Tuesday, August 01, 2017 | 564 | 690 | 584 | 602 | 684 | 521 |
Friday, September 01, 2017 | 547 | 596 | 552 | 632 | 566 | 527 |
Thursday, October 01, 2015 | 537 | 641 | 603 | 680 | 528 | 687 |
Tuesday, November 01, 2016 | 594 | 631 | 694 | 518 | 644 | 628 |
Monday, December 01, 2014 | 699 | 527 | 699 | 504 | 502 | 520 |
Saturday, February 01, 2014 | 561 | 537 | 638 | 651 | 526 | 609 |
Tuesday, March 01, 2016 | 686 | 589 | 582 | 647 | 620 | 570 |
Friday, May 01, 2015 | 603 | 578 | 660 | 519 | 662 | 653 |
Output-
Metric | Oct-16 | Nov-16 | Dec-16 | Jan-17 | Feb-17 | Mar-17 | Apr-17 | May-17 | Jun-17 | Jul-17 | Aug-17 | Sep-17 | 12 Month Avg |
KPI-1 | 691 | 575 | 596 | 512 | 534 | 520 | 636 | 665 | 639 | 675 | 564 | 547 | 594.4166667 |
KPI-2 | 585 | 516 | 615 | 600 | 520 | 531 | 683 | 542 | 517 | 665 | 690 | 596 | 10367.33333 |
KPI-3 | 501 | 636 | 608 | 699 | 691 | 663 | 627 | 542 | 679 | 528 | 584 | 552 | 186.75 |
calculated KPI-4 | 654 | 562 | 634 | 698 | 538 | 551 | 524 | 527 | 632 | 680 | 602 | 632 | 594.4166667 |
calculated KPI-5 | 565 | 648 | 537 | 572 | 614 | 561 | 509 | 571 | 559 | 653 | 684 | 566 | 781.1666667 |
calculated KPI-6 | 95.5% | 94.2% | 94.1% | 96.6% | 94.6% | 96.6% | 95.1% | 94.8% | 94.7% | 95.1% | 95.8% | 96.1% | 95.3% |
Solved! Go to Solution.
One way for your reference.
1.Unpivot the KPI columns.
2.Create a matrix with below measure, setting page level filter "is on or after" 2016-11-01.
Measure = IF ( HASONEVALUE ( yourTable[KPI] ) && HASONEVALUE ( yourTable[Date] ), SUM ( yourTable[Value] ), AVERAGE ( yourTable[Value] ) )
One way for your reference.
1.Unpivot the KPI columns.
2.Create a matrix with below measure, setting page level filter "is on or after" 2016-11-01.
Measure = IF ( HASONEVALUE ( yourTable[KPI] ) && HASONEVALUE ( yourTable[Date] ), SUM ( yourTable[Value] ), AVERAGE ( yourTable[Value] ) )
it worked, Thankyou so much 🙂