Forum Discussion
Anmolgan
Post Prodigy
Post ProdigyHow to pass date selections into custom column?
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 referen...
AnmolganPost Prodigy
Post Prodigyaz38 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
Community Champion
Community Championsee 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"
)