How to pass date selections into custom column?

[Anmolgan]

am using below dax to pass a date selection from query 1 date table into the dax custom column, but it seems like the selection is not working, how can I manage to do this:

Column1 = var reference_date= SELECTEDVALUE(Query1[FullDateAlternateKey])

return DATE(YEAR(reference_date),MONTH(reference_date),1)

[az38]

Hi @Anmolgan 

try  a measure

Measure = 
var reference_date= SELECTEDVALUE(Query1[FullDateAlternateKey])

return 
STARTOFMONTH(reference_date)

or column

Column1 = 
var reference_date= CALCULATE(MAX(Query1[FullDateAlternateKey]))

return 
STARTOFMONTH(reference_date)

[Anmolgan]

@az38 Says the reference date is an incorrect parameter defined

[Anmolgan]

@az38 It says reference date is an incorrect parameter defined

[az38]

@Anmolgan 

yep, my bad, sorry

you should use a measure

Measure = 
var reference_date= SELECTEDVALUE(Query1[FullDateAlternateKey])

return 
date(year(reference_date), MONTH(reference_date), 1)

[Anmolgan]

@az38 

can I not use this in a custom column? I have a datediff formula date works on the basis of this dax and that goes like this:

Month = DATEDIFF(pcc_view_ar_aging[effective_date], pcc_view_ar_aging[Measure],MONTH)

the above formula am trying to use here.

[Anmolgan]

@az38  so I am trying to calculate aging bucket on the basis of selection of a date field:

I have a custom column where I convert any selected that to 1st of each month, need to figure out how can I use custom column here:

Measure11 =

var reference_date= SELECTEDVALUE(Query1[FullDateAlternateKey])

return

date(year(reference_date), MONTH(reference_date), 1)

Above is a measure but I need to use some kind of custom column to convert the selected date into 1st of each month, after that I find the month differences using below:

Month = DATEDIFF(pcc_view_ar_aging[effective_date], [Measure],MONTH)

Above is a custom column for now, need to think of something if I need to use a measure for this,

After the above I finally calculate my aging bucket which is a custom column:

New aging bucket = SWITCH(TRUE(),MONTH(pcc_view_ar_aging[effective_date]) = MONTH(pcc_view_ar_aging[Measure11]) && YEAR(pcc_view_ar_aging[effective_date]) = YEAR(pcc_view_ar_aging[Measure11]) && pcc_view_ar_aging[effective_date] <= TODAY(), "Current", pcc_view_ar_aging[Month] =1 , "30 days", pcc_view_ar_aging[Month] =2 , "60 Days", pcc_view_ar_aging[Month] = 3 , "90 Days", pcc_view_ar_aging[Month] = 4,"120 Days", pcc_view_ar_aging[Month] = 5, "150 Days", pcc_view_ar_aging[Month] = 6, "180 Days", pcc_view_ar_aging[Month] = 7,"210 Days", pcc_view_ar_aging[Month] = 8,"240 Days", pcc_view_ar_aging[Month] = 9 , "270 Days", pcc_view_ar_aging[Month] = 10, "300 Days", pcc_view_ar_aging[Month] =11,"330 Days", pcc_view_ar_aging[Month] = 12, "360 Days", pcc_view_ar_aging[effective_date] < pcc_view_ar_aging[Measure11],"360+ Days", "greater than current month aging")

Now I need to solve all this as per the date selection in my powerbi visual do you have any idea how to solve this, I think If I figure out the selection of date in a custom column then I dont need to change each of the formulas, if I need to change everything as in a measure then I will need to change the main bucket formula accordingly, can you suggest any ways to do this??

[az38]

@Anmolgan 

see my post above.

your Month Measure will look like

Month = 
var _effDate = MAX(pcc_view_ar_aging[effective_date])

RETURN
DATEDIFF(_effDate , [Measure], MONTH)

Your New aging bucket measure will be similar with 

New aging bucket = 
var _Month = [Month]
var _effDate = MAX(pcc_view_ar_aging[effective_date])

RETURN
SWITCH(TRUE(),
MONTH(_effDate) = MONTH([Measure11]) && YEAR(_effDate) = YEAR([Measure11]) && _effDate <= TODAY(), "Current", 
_Month > 0 && _Month <= 12, CONCATENATE(30 * _Month, " Days"),
"greater than current month aging"
)

[Anmolgan]

@az38 I did not understand this line can you explain a bit, so that I can built the bucket and validate with the actual one in my column.

[az38]

@Anmolgan 

if you need your visual reacts to slicer you need to create a Measure and not a Column.

I do not completely understand your business logic, as I do not see any examples of data and desired result both. So, I Can not be sure in the correct statements for you

[Anmolgan]

@az38 I have attached an output screenshot of what I am getting when I am creating my bucket and passing a hardcoded date to refersh date, please view the screenshot:

[az38]

@Anmolgan
It’s not enough. What do you have in data source and what do you need as result?

[az38]

@Anmolgan 

you are trying to implement what-if logic which described here https://docs.microsoft.com/en-us/power-bi/desktop-what-if

it will work with measure

try to re-write your formula as a Measure

Month = 
var _effDate = MAX(pcc_view_ar_aging[effective_date])

RETURN
DATEDIFF(_effDate , [Measure], MONTH)