The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |