Forum Discussion

bedata1's avatar
bedata1
Frequent Visitor
3 years ago
Solved

Multiple Time Variables with If Return

Hi Community,

 

I want to create a DAX formula that shows the following:

If Date <= Today, then show the "amount" from the "G/L Entry" table and if Date > Today, then show the "amount" from the "Budget" table.

 

Date is linked to postingdate from the "G/L Entry" & "Budget" tables.

 

Visualization: Matrix with Month column

 

Folgende DAX habe ich erstellt, jedoch funktioniert diese nicht richtig:

IS LE = 
var TodayDate =
    TODAY()
var ActualAmount = 
    CALCULATE(SUM('G/L Entry'[amount]), FILTER('Date','Date'[Date] <= TODAY()))
var BudgetAmount =
    CALCULATE(SUM(Budget[Amount]),FILTER('Date','Date'[Date] > TODAY()))
return
IF(FILTER(Budget,Budget[Date] > TodayDate),BudgetAmount,ActualAmount)
 
Can anybody help me with creating this DAX formula?
 
Thank you for your help.

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi bedata1 

     

    try like:

    IS LE = 
    var TodayDate = TODAY()
    var ActualAmount = 
    CALCULATE(
        SUM('G/L Entry'[amount]),     FILTER('Date','Date'[Date] <= TODAY())
    )
    var BudgetAmount =    
    CALCULATE(
        SUM(Budget[Amount]),
        FILTER('Date','Date'[Date] > TODAY())
    )
    return
    IF(
        MAX(Budget[Date]) > TodayDate,
        BudgetAmount,
        ActualAmount
    )

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi bedata1 

     

    try like:

    IS LE = 
    var TodayDate = TODAY()
    var ActualAmount = 
    CALCULATE(
        SUM('G/L Entry'[amount]),     FILTER('Date','Date'[Date] <= TODAY())
    )
    var BudgetAmount =    
    CALCULATE(
        SUM(Budget[Amount]),
        FILTER('Date','Date'[Date] > TODAY())
    )
    return
    IF(
        MAX(Budget[Date]) > TodayDate,
        BudgetAmount,
        ActualAmount
    )