Forum Discussion
Helper IHOW TO CALCULATE ACTUAL SALES FOR A PARTICULAR MONTH
I have a table called Sales Summary, which is directly connected to SAP BW report. so it already have JAN-APR sales.
Another Table, called Target, where the target for each month and recorded.
I want to get below;
1. Calculate sales for a particular month by only calculating sales with date e.g (01.01.2023 to 31.01.2023, 01.02.2023 to 28.02.2023, 01.03.2023 to 31.03.2023, 01.04.2023 to 30.04.2023), so that i can get the percentage difference of actual vs target
2. to be able to use filter or slicer to spool from Sales Summary table, the actual sales and display it in the dashboard
I will appreciate if you can help
11 Replies
- DOLEARY85
Resident Rockstar
Hi,
do you have an example of how the data looks or able to share the PBIX file?
Without knowing the structure of the data I would assume you have a date field in the sales table. If you extract the month and year into separate fields then use something like the below to sum the sales for that particular month:
Measure 5 = CALCULATE(sum('Sales Summary'[Amount]),ALLEXCEPT('Sales Summary','Sales Summary'[Month],'Sales Summary'[Year])If I answered your question, please mark my post as solution, Appreciate your Kudos 👍 
TOYER01Like Sample Above. So i want it to calculate April Sales
- DOLEARY85
Hi,
try this measure:
Measure =CALCULATE(sum(Table1[Sales]),ALLEXCEPT(Table1,Table1[Date].[Month],Table1[Date].[Year]))this will provide a total sum for each month year, you'll need a field that only displays the Month and Year to summarise by in the table.I just created a calculated column based on the datat you shared for this:Column = Table1[Date].[Month] & " " & Table1[Date].[Year]If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
- TOYER01
Thank you for your response.
Please how do i create the calculated column.
I have created the measure so am lost after that.
- DOLEARY85
Where you click to create new measure, the option underneath should be new column
, then just use the formula
= Table1[Date].[Month] & " " & Table1[Date].[Year] to create the new date field
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
