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...
az38
Community Champion
Community Championyep, my bad, sorry
you should use a measure
Measure =
var reference_date= SELECTEDVALUE(Query1[FullDateAlternateKey])
return
date(year(reference_date), MONTH(reference_date), 1)
AnmolganPost Prodigy
Post Prodigyaz38
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.
- az38
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) - 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])returndate(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
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" )