Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to calculate actuals in Power BI (I TIPYCALLY USE EXCEL WITH PIVOTS), to calculate actuals this is the logic: Take the value in the intersection of Period 2023Q2, cycle 2023Q2 in source 1 Take the value in the intersection of Period 2023Q1, cycle 2023Q2 in source 2 Take the value in the intersection of Period 2022Q4, cycle 2023Q1 in source 2 Take the value in the intersection of Period 2022Q3, cycle 2022Q4 in source 2 Take the value in the intersection of Period 2022Q2, cycle 2022Q3 in source 2 Take the value in the intersection of Period 2022Q1, cycle 2022Q2 in source 2, etc
Source 2 has 14 cycles and 26 periods (14 for history and 12 for forecast)
Source 1 has only 1 cycle (current or latest) and 14 periods (all of them history)
The source data has also other 5 'sources' or models with information but to get data I need only those 2, so actuals for all 7 sources need to be the same as the logic explained above
I need this to be done in a dynamic way because every quarter I get new data so the cycles will update and the periods as well for all sources, however the structure won't. Source 1 will still have 1 cycle and 14 historical periods, all the other sources will have 14 cycles and 26 periods (14 history and 12 forecast)
any advice helps, I've tried chat GPT but I have gotten nowhere
@AldoMF can you check desired output, period seems to be wrong.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
sorry for the confusion, it's correct now in the previous reply
This is a sample data with in a pivot table from excel:
Sum of Units | Period | ||||||||||||||||||||||||||
Source | Cycle | 2020Q1 | 2020Q2 | 2020Q3 | 2020Q4 | 2021Q1 | 2021Q2 | 2021Q3 | 2021Q4 | 2022Q1 | 2022Q2 | 2022Q3 | 2022Q4 | 2023Q1 | 2023Q2 | 2023Q3 | 2023Q4 | 2024Q1 | 2024Q2 | 2024Q3 | 2024Q4 | 2025Q1 | 2025Q2 | 2025Q3 | 2025Q4 | 2026Q1 | 2026Q2 |
Source 2 | 2020Q1 | 757 | 772 | 501 | 791 | 661 | 538 | 569 | 784 | 671 | 551 | 715 | 517 | 552 | 714 | 502 | 798 | 761 | 536 | 566 | 650 | 636 | 790 | 518 | 573 | ||
Source 2 | 2020Q2 | 674 | 675 | 627 | 567 | 693 | 745 | 685 | 580 | 730 | 744 | 665 | 596 | 753 | 580 | 742 | 681 | 682 | 605 | 535 | 741 | 734 | 694 | 779 | 697 | ||
Source 2 | 2020Q3 | 611 | 554 | 775 | 506 | 640 | 560 | 606 | 583 | 785 | 526 | 740 | 747 | 727 | 777 | 729 | 737 | 711 | 715 | 602 | 591 | 771 | 529 | 644 | 647 | ||
Source 2 | 2020Q4 | 561 | 682 | 727 | 586 | 767 | 578 | 673 | 603 | 731 | 596 | 723 | 624 | 682 | 717 | 528 | 554 | 752 | 696 | 570 | 699 | 512 | 630 | 581 | 557 | ||
Source 2 | 2021Q1 | 549 | 609 | 685 | 696 | 749 | 730 | 741 | 800 | 786 | 789 | 557 | 579 | 531 | 579 | 741 | 501 | 673 | 784 | 624 | 715 | 665 | 593 | 630 | 755 | 643 | 534 |
Source 2 | 2021Q2 | 650 | 694 | 612 | 681 | 766 | 791 | 653 | 554 | 732 | 627 | 698 | 663 | 769 | 627 | 618 | 520 | 551 | 589 | 769 | 709 | 732 | 534 | 761 | 520 | 640 | 543 |
Source 2 | 2021Q3 | 591 | 738 | 537 | 761 | 785 | 714 | 518 | 700 | 721 | 746 | 771 | 653 | 626 | 697 | 716 | 602 | 517 | 521 | 739 | 674 | 764 | 727 | 721 | 728 | 593 | 644 |
Source 2 | 2021Q4 | 529 | 739 | 510 | 714 | 737 | 701 | 680 | 756 | 664 | 688 | 612 | 521 | 740 | 590 | 657 | 545 | 667 | 606 | 634 | 547 | 647 | 571 | 789 | 690 | 646 | 502 |
Source 2 | 2022Q1 | 679 | 762 | 770 | 565 | 752 | 791 | 516 | 506 | 787 | 526 | 785 | 715 | 509 | 516 | 648 | 637 | 670 | 752 | 727 | 653 | 600 | 623 | 686 | 782 | 782 | 638 |
Source 2 | 2022Q2 | 504 | 796 | 578 | 780 | 684 | 744 | 754 | 630 | 763 | 725 | 644 | 622 | 659 | 783 | 695 | 544 | 622 | 516 | 522 | 692 | 596 | 743 | 520 | 551 | 651 | 640 |
Source 2 | 2022Q3 | 594 | 654 | 787 | 794 | 679 | 577 | 800 | 538 | 525 | 709 | 519 | 769 | 548 | 515 | 726 | 782 | 603 | 571 | 541 | 572 | 552 | 566 | 747 | 615 | 657 | 599 |
Source 2 | 2022Q4 | 691 | 785 | 699 | 633 | 556 | 522 | 699 | 765 | 794 | 590 | 729 | 540 | 580 | 747 | 585 | 657 | 507 | 690 | 589 | 524 | 583 | 686 | 610 | 625 | 571 | 557 |
Source 2 | 2023Q1 | 556 | 752 | 783 | 526 | 536 | 558 | 555 | 748 | 602 | 541 | 678 | 518 | 520 | 542 | 622 | 799 | 757 | 756 | 621 | 684 | 748 | 666 | 541 | 568 | 740 | 543 |
Source 2 | 2023Q2 | 776 | 705 | 712 | 769 | 675 | 660 | 776 | 668 | 706 | 557 | 744 | 583 | 733 | 777 | 519 | 775 | 552 | 669 | 614 | 696 | 509 | 650 | 501 | 664 | 531 | 524 |
Source 1 | 2023Q2 | 577 | 528 | 690 | 642 | 726 | 681 | 735 | 659 | 616 | 536 | 719 | 707 | 661 | 748 |
The desired outcome is this: (also marked in blue in the above table)
Actuals | |
Period | Units |
2020Q1 | 674 |
2020Q2 | 554 |
2020Q3 | 727 |
2020Q4 | 696 |
2021Q1 | 766 |
2021Q2 | 714 |
2021Q3 | 680 |
2021Q4 | 506 |
2022Q1 | 763 |
2022Q2 | 709 |
2022Q3 | 729 |
2022Q4 | 518 |
2023Q1 | 733 |
2023Q2 | 748 |
@AldoMF Please provide some data with the expected output Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |