Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |