Forum Discussion

Bokazoit's avatar
Bokazoit
Icon for Continued Contributor rankContinued Contributor
10 months ago
Solved

Measure to use different values depending on dates

I have this data model:   One measure for the table FactFinans: SUM('FactFinans (HF)'[#Bogførtbeløb])*-1 And another measure for FactEstimat: Sum('FactEstimat (HF)'[#Estimat]) Dependi...
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    10 months ago
    Combined Amount =
    VAR EstimatQuarter = MID(MAX('FactEstimat (HF)'[Estimat version]), 2, 1) * 1
    VAR EstimatYear = MID(MAX('FactEstimat (HF)'[Estimat version]), 4, 4)
    VAR EndMonth = SWITCH(TRUE(), EstimatQuarter = 2, 3, EstimatQuarter = 3, 6, EstimatQuarter = 4, 9)
    VAR ActualFinansDate = EOMONTH(DATE(EstimatYear, EndMonth, 1), 0)
    RETURN
    CALCULATE(
    SUM('FactFinans (HF)'[#Bogførtbeløb]) * -1,
    'FactFinans (HF)'[Financial Date] <= ActualFinansDate
    ) +
    CALCULATE(
    SUM('FactEstimat (HF)'[#Estimat]),
    'FactEstimat (HF)'[Financial Date] > ActualFinansDate
    )
Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
10 months ago
Combined Amount =
VAR EstimatQuarter = MID(MAX('FactEstimat (HF)'[Estimat version]), 2, 1) * 1
VAR EstimatYear = MID(MAX('FactEstimat (HF)'[Estimat version]), 4, 4)
VAR EndMonth = SWITCH(TRUE(), EstimatQuarter = 2, 3, EstimatQuarter = 3, 6, EstimatQuarter = 4, 9)
VAR ActualFinansDate = EOMONTH(DATE(EstimatYear, EndMonth, 1), 0)
RETURN
CALCULATE(
SUM('FactFinans (HF)'[#Bogførtbeløb]) * -1,
'FactFinans (HF)'[Financial Date] <= ActualFinansDate
) +
CALCULATE(
SUM('FactEstimat (HF)'[#Estimat]),
'FactEstimat (HF)'[Financial Date] > ActualFinansDate
)