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.
Good morning.
I have a question on how I can calculate the difference between values of a product in different months, for analysis of cost reduction or an increase.
Thank you very much for your attention.
Solved! Go to Solution.
Hi , @GabrielGreg
According to your description, you want to "calculate the difference between values of a product in different months". I have no sample data so i create some test data in my side , you can refer to i hope it can help you.
(1)This is my test data:
(2)We can create two date table as slicers and we do not need to create ant relationship between the tables.
We can click "New Table" and enter this:
Date = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date])) ,"Year" , YEAR([Date]) ,"Month" , MONTH([Date]))
Date 2 = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date])) ,"Year" , YEAR([Date]) ,"Month" , MONTH([Date]))
(3)Then we can create three measures like this:
Left_Slicer = var _t = FILTER('Table' , YEAR('Table'[Date]) = MAX('Date'[Year]) && MONTH('Table'[Date]) = MAX('Date'[Month]))
return
SUMX(_t , [Value])
Right_Slicer = var _t = FILTER('Table' , YEAR('Table'[Date]) = MAX('Date 2'[Year]) && MONTH('Table'[Date]) = MAX('Date 2'[Month]))
return
SUMX(_t , [Value])
Differences = [Right_Slicer] - [Left_Slicer]
(4)Then we can put the fields we need on the visual and we can select the slicer to select the months we need to compare and then we can get the differnece in the table visual:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @GabrielGreg
According to your description, you want to "calculate the difference between values of a product in different months". I have no sample data so i create some test data in my side , you can refer to i hope it can help you.
(1)This is my test data:
(2)We can create two date table as slicers and we do not need to create ant relationship between the tables.
We can click "New Table" and enter this:
Date = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date])) ,"Year" , YEAR([Date]) ,"Month" , MONTH([Date]))
Date 2 = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date])) ,"Year" , YEAR([Date]) ,"Month" , MONTH([Date]))
(3)Then we can create three measures like this:
Left_Slicer = var _t = FILTER('Table' , YEAR('Table'[Date]) = MAX('Date'[Year]) && MONTH('Table'[Date]) = MAX('Date'[Month]))
return
SUMX(_t , [Value])
Right_Slicer = var _t = FILTER('Table' , YEAR('Table'[Date]) = MAX('Date 2'[Year]) && MONTH('Table'[Date]) = MAX('Date 2'[Month]))
return
SUMX(_t , [Value])
Differences = [Right_Slicer] - [Left_Slicer]
(4)Then we can put the fields we need on the visual and we can select the slicer to select the months we need to compare and then we can get the differnece in the table visual:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |