Need help adding a date measure to finish off what I already have

[RichOB]

Hi, my end goal here is to get the % from dividing the Total_Loss / Total Spent within the 01/04/2024 - 01/06/2024 date range.

Category name	Category	Date	Credit	Debit	Amount
Spent 1	1	01/042024	-25	0	-25
Spent 1	1	01/05/2024	-40	5	-45
Spent 2	2	01/06/2024	0	100	-100
Spent 2	2	01/07/2024	5	15	-10
Loss 1	3	01/042024	20	4	16
Loss 1	3	01/05/2024	-12	30	-42
Loss 2	4	01/06/2024	20	0	20
Loss 2	4	01/07/2024	30	28	2

So far I have added an Amount column by subtracting the credit from the debit columns. (keep in mind this is fake data, the theory behind it is what I need) Then I:

1. Combined Category 1+2 in 1 measure:

Total_Spent = 

CALCULATE(

    SUM(Sheet[Amount]),

    KEEPFILTERS(Sheet[Category] IN {"1","2"}))

2. Combined Category 3+4 in another measure:

Total_Loss = 

CALCULATE(

    SUM(Sheet[Amount]),

    KEEPFILTERS(Sheet[Category] IN {"3","4"}))

3.  Amount_% = [Total_Loss] - [Total_Spent]. This gives me the end figures and % I  need

4. I'm having trouble with step 4 putting a 01/04/2024 - 01/06/2024 date range around it. Can you help with this end part, please?

Thanks

[v-yohua-msft · Accepted solution]

Hi, @RichOB 

Try the following measures:

Total_Loss = 
CALCULATE(
    SUM('Table'[Amount]),
    KEEPFILTERS('Table'[Category] IN {3, 4}),
    DATESBETWEEN('Table'[Date], DATE(2024, 4, 1), DATE(2024, 6, 1))
)

Total_Spent = 
CALCULATE(
    SUM('Table'[Amount]),
    KEEPFILTERS('Table'[Category] IN {1, 2}),
    DATESBETWEEN('Table'[Date], DATE(2024, 4, 1), DATE(2024, 6, 1))
)

Amount_% = 
DIVIDE([Total_Loss], [Total_Spent], 0)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[ray_aramburo]

Assuming you want to specifically hardcode that period, then it would be:

Amonunt%InPeriod = CALCULATE([Amount_%], DATESBETWEEN('TableName'[Date], DATE(2024,4,1), DATE(2024,6,1)))