Measure to use different values depending on dates

[Bokazoit]

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])

Depending on the 'estimate version' I calculate a date (a quarter). If the choosen 'estimate version' is 'Q4 2025' I calculate a date to split what measure to use:

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)

From here I can't make it work. I would like the measure to use this:

SUM('FactFinans (HF)'[#Bogførtbeløb])*-1

when the financial dates in FactFinans is before ActualFinansDate else use:

Sum('FactEstimat (HF)'[#Estimat])

from FactEstimat

[v-veshwara-msft]

Hi @Bokazoit ,
Just checking in to see if you had a chance to go through the response shared by @Kedar_Pande .

Let us know if you need any additional clarification or have further queries.

[V-yubandi-msft]

Hi @Bokazoit ,

I wanted to check if you had the opportunity to review the information provided by @Kedar_Pande . Please feel free to contact us if you have any further questions. 
 

Thank you and continue using Microsoft Fabric Community Forum.

[Kedar_Pande · Accepted solution]

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]

@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

[Bokazoit]

That looks AI generated?

I asked CoPilot and it returned that exact answer...