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

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

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
    )

5 Replies

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

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

      That looks AI generated?

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

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    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.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    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.