Sum of Value between Slicer Dates

Question

I want to get the sum between two slicer dates without breaking it out by month. The number should be static for the dates selected.

Here's an example of the data I'm trying to get information from:

Date	Disposal Method	Weight (lb)
1/1/2024	Recycle	150
1/1/2024	Landfill	100
2/1/2024	Recycle	225
2/1/2024	Landfill	175
3/1/2024	Recycle	125
3/1/2024	Landfill	75
TOTAL		850

Date	Production (lbs)
1/1/24	750
2/1/24	500
3/1/24	75
TOTAL	1,325

I have a _Calendar table for 1/1/24 - 12/31/24.

I want to get the landfill intensity (landfill lbs/production lbs to be dynamic with a date slicer. If the dates selected were 1/1/2024 - 2/28/24. The static intensity should be (100 [Jan landfill] + 175 [Feb landfill]) / (750 [Jan Prod] + 500 [Feb Prod]) = 0.22

I'm trying to get the following table:

Date	Landfill Intensity of Dates Selected
1/1/24	0.22
2/1/24	0.22

I've tried this:

Landfill_Intensity = 
    CALCULATE(
        sum('Waste'[Weight (lbs)]),
            'Waste'[Disposal Method] = "landfill",
            DATESBETWEEN('Waste'[Date],FIRSTDATE(_Calendar[Date]),LASTDATE(_Calendar[Date]))      
    )    

/

CALCULATE(
    sum(Production[Weight (lbs)]), 
        DATESBETWEEN(Production[Date], FIRSTDATE(_Calendar[Date]),LASTDATE(_Calendar[Date]))
    )

and the intensity is still dependent on date:

Date	Landfill Intensity of Dates Selected
1/1/24	0.13 [100/750]
2/1/24	0.35 [175/500]

How can I make the intensity take values in the slicer but apply a static value for values within the date slicer?

mkRabbani · Accepted Answer · 2024-10-10 11:26:09Z

0

Can you try this below-

Landfill_Intensity = 


CALCULATE(
    sum('Waste'[Weight (lb)]),
    'Waste'[Disposal Method] = "landfill",
    DATESBETWEEN(
        'Waste'[Date],
        FIRSTDATE('calendar'[Date]),
        LASTDATE('calendar'[Date])
    )        
)    

/

CALCULATE(
    sum(Production[Production (lbs)]), 
    FILTER(
        ALL(Production[Date]),
        Production[Date] >= FIRSTDATE('calendar'[Date])
            && Production[Date] <= LASTDATE('calendar'[Date])
        
    )
)

Output-

Hope your Production and Waste tables are connected to _calendar table this below way using Date column-

edited Oct 10, 2024 at 11:26

answered Oct 9, 2024 at 11:24

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum of Value between Slicer Dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related