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
    )
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
10 months ago

Bokazoit Try using Calculate the ActualFinansDate

dax
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 LastDayInMonth = DAY(EOMONTH(DATE(EstimatYear, EndMonth, 1), 0))
VAR ActualFinansDate = DATE(EstimatYear, EndMonth, LastDayInMonth)

 

 

You want to sum FactFinans up to ActualFinansDate, and FactEstimat after that. You can use CALCULATE with FILTER to achieve this:

dax
Combined Measure =
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 LastDayInMonth = DAY(EOMONTH(DATE(EstimatYear, EndMonth, 1), 0))
VAR ActualFinansDate = DATE(EstimatYear, EndMonth, LastDayInMonth)
VAR FinansSum =
CALCULATE(
SUM('FactFinans (HF)'[#Bogførtbeløb]) * -1,
FILTER(
ALL('FactFinans (HF)'),
'FactFinans (HF)'[Dato] <= ActualFinansDate
)
)
VAR EstimatSum =
CALCULATE(
SUM('FactEstimat (HF)'[#Estimat]),
FILTER(
ALL('FactEstimat (HF)'),
'FactEstimat (HF)'[Dato] > ActualFinansDate
)
)
RETURN
FinansSum + EstimatSum