Dax query as a column for date between 2W and 1M, 1M and 3M, etc...

[Shine_456]

Hi All,

Can anyone help me on how to get number of users between the date range of 2week and 1Month, 1Month and 3 Month, 3Month and 1 Year others as more than 1 year as abar chart.

I need a query for date ranges as mentioned above.

Thank you.

[AllisonKennedy · Accepted solution]

@Shine_456  I would suggest doing this in Power Query rather than DAX. 

Click Transform Data, Add Column > Custom Column. 

Then put a code similar to this (replace [Date] with the name of your date column and replace the ranges and text with what you want). 

if [Date] >= Date.AddWeeks( Date.From( DateTime.FixedLocalNow() ) , -2) then "2 weeks"
else if [Date] >= Date.AddMonths( Date.From( DateTime.FixedLocalNow() ) , -1) then "2 weeks - 1 month"
else if [Date] >= Date.AddMonths( Date.From( DateTime.FixedLocalNow() ) , -3) then "1 - 3 months"
else if [Date] >= Date.AddMonths( Date.From( DateTime.FixedLocalNow() ) , -12) then "3 - 12 months"
else "Older than 1 year"

[Shine_456]

Thanks @AllisonKennedy . Your query worked.

I had some null in date. How to replace them from above query?

[AllisonKennedy]

@Shine_456 , what do you want to replace the null with?

you can either do this as a separate step first using 'replace values' and type 'null' in the value to find/replace box, or you can add a line at the start of the new column query: 

if [Date] = null then "NA"

else if [Date] >= Date.AddWeeks( Date.From( DateTime.FixedLocalNow() ) , -2) then "2 weeks"
else if [Date] >= Date.AddMonths( Date.From( DateTime.FixedLocalNow() ) , -1) then "2 weeks - 1 month"
else if [Date] >= Date.AddMonths( Date.From( DateTime.FixedLocalNow() ) , -3) then "1 - 3 months"
else if [Date] >= Date.AddMonths( Date.From( DateTime.FixedLocalNow() ) , -12) then "3 - 12 months"
else "Older than 1 year"