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.
Hello,
I'm trying to use more Power BI in my daily use. I have a striaght forward use case but I need help exectuing.
My data is structured and loads into PBI error free. Here is my data.
My Goal: Summarize tax by month and calculate a variance between most recent month and prior month. This is how it is current performed in excel.
I've tried pivoting month and tax into their own columns based on other forum posts. This has mostly worked. My issue is I can not get the data to dynamically update for the next months data.
What would be the recommended steps to accomplish this? Should I continue with the pivoted columns or is there a better way to summarize this data.
Any help would be appreciated!
Thanks!
Solved! Go to Solution.
Hi @bjsrm8 ,
In Power BI, you could create measure to achive your desired output.
Assuming that you have data sample like below.
Code Period Tax
471 | February 2019 | 10 |
471 | February 2019 | 20 |
471 | February 2019 | 30 |
471 | March 2019 | 40 |
471 | March 2019 | 50 |
471 | March 2019 | 60 |
472 | February 2019 | 25 |
472 | February 2019 | 35 |
472 | February 2019 | 45 |
472 | March 2019 | 20 |
472 | March 2019 | 25 |
472 | March 2019 | 30 |
Please create the measure below.
thismonth = CALCULATE ( SUM ( Table1[Tax] ), FILTER ( 'Table1', 'Table1'[Period].[MonthNo] = MONTH ( TODAY () ) && 'Table1'[Code] = MAX ( 'Table1'[Code] ) ) ) previous_month = VAR thismonth = MONTH ( TODAY () ) VAR previous_month = IF ( thismonth = 1, 12, thismonth - 1 ) RETURN CALCULATE ( SUM ( Table1[Tax] ), FILTER ( 'Table1', 'Table1'[Period].[MonthNo] = previous_month && 'Table1'[Code] = MAX ( 'Table1'[Code] ) ) ) Difference = 'Table1'[thismonth]-'Table1'[previous_month]
Here is the output.
More details, please refer to my attachement.
Best Regards,
Cherry
Hi @bjsrm8 ,
In Power BI, you could create measure to achive your desired output.
Assuming that you have data sample like below.
Code Period Tax
471 | February 2019 | 10 |
471 | February 2019 | 20 |
471 | February 2019 | 30 |
471 | March 2019 | 40 |
471 | March 2019 | 50 |
471 | March 2019 | 60 |
472 | February 2019 | 25 |
472 | February 2019 | 35 |
472 | February 2019 | 45 |
472 | March 2019 | 20 |
472 | March 2019 | 25 |
472 | March 2019 | 30 |
Please create the measure below.
thismonth = CALCULATE ( SUM ( Table1[Tax] ), FILTER ( 'Table1', 'Table1'[Period].[MonthNo] = MONTH ( TODAY () ) && 'Table1'[Code] = MAX ( 'Table1'[Code] ) ) ) previous_month = VAR thismonth = MONTH ( TODAY () ) VAR previous_month = IF ( thismonth = 1, 12, thismonth - 1 ) RETURN CALCULATE ( SUM ( Table1[Tax] ), FILTER ( 'Table1', 'Table1'[Period].[MonthNo] = previous_month && 'Table1'[Code] = MAX ( 'Table1'[Code] ) ) ) Difference = 'Table1'[thismonth]-'Table1'[previous_month]
Here is the output.
More details, please refer to my attachement.
Best Regards,
Cherry
Thank you for helping me! I greatly appreciate it.