topic Sales Figure between two date columns and two sales columns in DAX Commands and Tips

Sales Figure between two date columns and two sales columns

dhodgson — Mon, 01 Jul 2024 06:04:38 GMT

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,

Re: Sales Figure between two date columns and two sales columns

aduguid — Mon, 01 Jul 2024 07:19:32 GMT

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) ) )

Re: Sales Figure between two date columns and two sales columns

Joe_Barry — Mon, 01 Jul 2024 09:20:57 GMT

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

Re: Sales Figure between two date columns and two sales columns

Anonymous — Tue, 02 Jul 2024 05:49:55 GMT

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.