Populating column based another column based on date

[Anonymous]

Hey I am new to power bi and have searched for this answer before making this post:

I am trying to populate a column based on another column and filtering whether a date column is filled out.

I want to pull the amount field only if the date field is filled out.

New_Value = 

CALCULATE ( CONTRACTS[DATE] <= TODAY() && CONTRACTS[ACTUAL_AMOUNT] <> BLANK

RETURN

CALCULATE ( SELECTEDVALUE ( CONTRACTS[ACTUAL_AMOUNT] )

I have gotten it to return True or False values but I need the value of the Actual Amount field to populate if their is a date field present.

[Whitewater100 · Accepted solution]

Hi:

Can you try a couple of measures? The first one to sum the contract amount.

Contract Total = SUM(CONTRACTS[ACTUAL_AMOUNT] )

New Measure = 
     var contractdate = SELECTEDVALUE(Contracts[Date])

     return

    IF([Contract Total] > 0, CALCULATE([Contract Total], FILTER(ALL(Contracts), 
                                       Contracts[Date] <= TODAY()
)
) 

I 'm not sure if you have a separate DateTable, but you should, marked as date table and with realtionship on Date field to Contract table Date field. I'll paste a DAX Date Table you can use. Go to Modeling>NEW TABLE then paste what is at bottom of message.

Your formulas would change slightly.

IF([Contract Total] > 0, CALCULATE([Contract Total], FILTER(ALL(Dates), 
                                       Contracts[Date] <= TODAY()

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

I hope this helps!

[Anonymous]

Thank you so much!! I took the snip at the top and made this and it worked.

New_Value = var contractdate = SELECTEDVALUE(CONTRACTS[DATE])

return

IF(CONTRACTS[ACTUAL_AMOUNT] > 0, CALCULATE(SUM(CONTRACTS[ACTUAL_AMOUNT]),

CONTRACTS[DATE] <= TODAY()))