Sales Figure between two date columns and two sales columns

[dhodgson]

Hi 

I need to calculate the total sales between two dates and this comes from 2x revenue columns & 2 different date columns, example below.

What formula can I use to have the SCDATE & INVDATE search between 01/07/2023 – 30/06/2024 and then sum SCTOTAL & INVVAL to find the final value.

SERVCARDID	SCTOTAL	SCDATE	INVVALUE	INVDATE
123546			30.56	26/05/2024
789456	115.30	1/03/2024
947707	20.8	28/06/2024

Thanks, 

[Anonymous · Accepted solution]

Hi @dhodgson ,

I made simple samples and you can check the results below:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry]

Hi @dhodgson 

Should the time frame be dynamic?  If you have a date table in your data model, this solution is dynamic.

• Create a one to many relationship from the Date table date column to the SCDATE column in your table, this should be an active relationship
• Repeat the same but this time to the INVDATE column, this relationship will be automatically be in active, leave it rthis way as it's expected behaviour
• You van combine these into one measure, but as good practice, I will like to separate my measure out

SC Sales = SUM(Table1[SCTotal])​

Invoice Sales =
CALCULATE(
   SUM(Table1[INVVALUE]), 
   USERELATIONSHIP(Date[Date], Table1[INVDATE])​

Total Sales =
[SC Sales] + [Invoice Sales]

• Create a Date slice, using the date column from the Date table. Then add your measure to any visual. If you are looking at visuals over time, use the date related context from the Date table.

Hope this helps

Joe

[aduguid]

Measure for SCTOTAL filtered by SCDATE

Total_SCTOTAL = 
CALCULATE(
    SUM('YourTable'[SCTOTAL]),
    FILTER(
        'YourTable',
        'YourTable'[SCDATE] >= DATE(2023, 7, 1) &&
        'YourTable'[SCDATE] <= DATE(2024, 6, 30)
    )
)

Measure for INVVALUE filtered by INVDATE

Total_INVVALUE = 
CALCULATE(
    SUM('YourTable'[INVVALUE]),
    FILTER(
        'YourTable',
        'YourTable'[INVDATE] >= DATE(2023, 7, 1) &&
        'YourTable'[INVDATE] <= DATE(2024, 6, 30)
    )
)