Dynamic measure with date slicers

[AlexXandyr]

Hello, 
I have encountered a problem with my report. Im trying to show some KPIs for couple different warehouses where I base it on some variable cost and fixed variables with possibility to use a month slicer. 

		Warehouse 1	Warehouse 2	Comment
Variable cost	Cost for storage	X	Y	Variable, and based on sum measure (Cost for Storage_m = CALCULATE(sum(Actuals[Amount_r]),Actuals[Cost type] ="Cost for Storage")
Fixed cost	SQM cost	12000	14000	Fixed per warehouse
Problem formula	Cost for storage/SQM	sum of X/12000	sum of Y/14000	Formula, which works fine when choosing one month but not when leaving month empty or multiple months
Correct formula	Cost for storage/SQM	- if 3 months chosen = (X/3) / SQM cost	- if no months chosen = (Y/12) / SQM cost	Slicer needs to change the the Cost for storage/SQM formula to divide with count of how many months thats been chosen

[AlexXandyr · Accepted solution]

I found a solution where I fixed the Cost for Storage part (making it an average based on the chosen month slicer), which then made the Cost per SQM calculation correct

Cost per Storage=

VAR _countofselmonth =

    CALCULATE ( DISTINCTCOUNT ( 'Calendar'[MonthName] ), ALLSELECTED ( 'Calendar' ) )

    RETURN

    DIVIDE (CALCULATE(sum(Actuals[Amount]),Actuals[Cost type] ="Cost for Storage"),  _countofselmonth )

[MFelix]

Hi @AlexXandyr ,

Not sure if I understood the question correctly but you need to adjust your values for the fixed part to be over the full selected months something similar to:

Cost for Storage / SQM = DIVIDE ([Cost for Storage], SUMX((Calendar[Months], 12000)

[AlexXandyr]

Thank you, sorry for not being clear. Please see below where I try to explain in another way

The Cost per SQM is actually an average of the months chosen in the slicer.