Sum of total sales to date for any "jobs" worked on in month (or selected date range)

[alann]

Hi, First post here, so hopefully I explain myself well.

I have a requirement whereby I need to calculate the SUM(Sales) up-to-and-including (i.e. <=) MAX(selected/filtered date) where the "Job Number" is a job number that was sold in the selected/filtered date range.

Background

- A "Job", or project, will go over many months. Some jobs have sales every calendar month and some jobs have calendar months where no work is invoiced.

- A "job" is always invoiced to a single customer, though a single customer can/does have many jobs on the go at all times

Requirement

• A table or chart with Customer as the dimension, showing:
  • SUM(Sales) for the dimension customer for the selected/filtered Month
  • SUM(Sales) <= max(selected/filtered Date) for the dimension customer, for any Jobs that were sold within the selected/filtered Month.

Example data

Filtering for Feb would show

Customer, Sales Month-to-Date, Sales Job-to-Date

A, 200, 400

B, 0, 0

Mar would show

Customer, Sales Month-to-Date, Sales Job-to-Date

A, 100, 300 (only Job# 3 was sold in March, therefore only Job# 3 in Sales Job-to-Date column)

B, 100, 200

Assume my Transaction table is simple: Customer_Number, Job_Number, Transaction_Date, Sales_Amount Calendar Table also simple: Transaction_Date, Year, Month, Day, etc.

Thanks in advance.

[v-shex-msft]

Hi @alann ,

You can try to use following measure formula if it works:

Measure =
VAR selected =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] <= selected ),
        VALUES ( Table[Customer_Number] ),
        VALUES ( Table[Job_Number] )
    )

If above not help, please share a pbix file with sample data and expected result for test.

Notice:

1. Calendar is a date table without relation to original table.

2. Do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

[alann]

Hi @v-shex-msft

Thanks for your response. I was able to construct the formula i needed from your reply. The only change i made was to remove th VALUES ( Table[Customer_Number] ) as this was my tables dimension anyway.

I also replaced ALLSELECTED with ALL to change the bahviaour of my date filtering slightly.

Thanks again.

[alann]

Sorry this didn't quite get the result i was looking for after-all.

I cannot see where to attach a file so see screen shots below:

1. Source table TableName = "My_Table"

2. Measure

3. Results when selecting Calendar2.Year = 2019 and Calendar2.Month = Feb

Because Customer_Number 'B' didn't have any sales in Feb-2019, the result of their "Measure" (or "sales-to-date") should be zero (0). Once i select Mar-2019 the result of their measure should then be 200 as they have sales in March.

I'll continue to try attach my example .PBIX file, but hope this provides enough info to set-up example in the interim.

Thanks