I need to apply two different filters to a measure. I can do them individually but cannot figure out how to combine them. I want to get total pounds of linen per month.
For a table "Forecast"
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]Member
[/TD]
[TD]Linen Type
[/TD]
[TD]Activity
[/TD]
[TD]Weight
[/TD]
[TD]Linen Group
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Wash
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Dry
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Iron
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019[/TD]
[TD]Member A[/TD]
[TD]Gowns[/TD]
[TD]Wash[/TD]
[TD]700[/TD]
[TD]MemberA_Gowns[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a monthly forecast of laundry usage by cooperative member, but the core information is at a different level of aggregation. Each type of linen (e.g. scrubs) goes through multiple actions such as washing, drying, and folding. The data provides the total pounds of scrubs, so if we have 500 lbs of scrubs that's 500 lbs for each of the activities but still only 500 lbs total for the member.
The data only has rows for the start date. Member A will continue to process 500 lbs of scrubs a month.
I have a measure that will project the usage into the future.
But it's adding the all the weights together for the months (e.g. giving me 1,500 lbs instead of 500).
I have a measure that will properly group the weight
But it's not projecting into the future. How can I combine these measures - or have a new one - that will both sum the weight as well as project into the future? Thanks.
For a table "Forecast"
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]Member
[/TD]
[TD]Linen Type
[/TD]
[TD]Activity
[/TD]
[TD]Weight
[/TD]
[TD]Linen Group
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Wash
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Dry
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Iron
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019[/TD]
[TD]Member A[/TD]
[TD]Gowns[/TD]
[TD]Wash[/TD]
[TD]700[/TD]
[TD]MemberA_Gowns[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a monthly forecast of laundry usage by cooperative member, but the core information is at a different level of aggregation. Each type of linen (e.g. scrubs) goes through multiple actions such as washing, drying, and folding. The data provides the total pounds of scrubs, so if we have 500 lbs of scrubs that's 500 lbs for each of the activities but still only 500 lbs total for the member.
The data only has rows for the start date. Member A will continue to process 500 lbs of scrubs a month.
I have a measure that will project the usage into the future.
Code:
Monthly Forecast Lbs:=SUMX (
CALCULATETABLE (
Forecast,
FILTER ( Forecast, Forecast[Start Date] <= MAX ( 'Calendar'[Date] ) )
),
Forecast[Weight]
)
But it's adding the all the weights together for the months (e.g. giving me 1,500 lbs instead of 500).
I have a measure that will properly group the weight
Code:
Forecast Lbs:=SUMX (
VALUES (Forecast[Linen Group]),
CALCULATE ( MAX (Forecast[Weight] )
)
)
But it's not projecting into the future. How can I combine these measures - or have a new one - that will both sum the weight as well as project into the future? Thanks.