HOW TO CALCULATE ACTUAL SALES FOR A PARTICULAR MONTH

[TOYER01]

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

[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 👍

[TOYER01]

This is where i hang, could not go further

[DOLEARY85]

I've uploaded an example based on the first 5 rows of your data here:

Power BI File

see if this helps

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[TOYER01]

Yes, I saw it, this is viewed on a slicer or filter.

What am requesting is, I want the actual sales for each month in a table, having 2 columns:  Month & Sales

In my screenshot,

Sales Summary table contain actual sales from JAN till date

Target Table contain targets of each state

achievement table will contain the summary of sales derived from Sales Summary Table, it will have 2 columns (Month and Sales)

Apologies for giving you back and forth on this, am still a newbee

[DOLEARY85]

No problem, 

1. So if you go to report view:
2. create the new column by right clicking on the table on the right hand side.
3. Enter the formula:     Month Year = Table1[Date].[Month] & " " & Table1[Date].[Year]
4. add this column to the table visualisation
5. then add the measure you previously created to the table too

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[TOYER01]

Like Sample Above. So i want it to calculate April Sales

[DOLEARY85]

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 👍

[TOYER01]

Like Sample Above. So i want it to calculate April Sales

[DOLEARY85]

Have a look here:

Power BI File 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍