topic Dax query as a column for date between 2W and 1M, 1M and 3M, etc... in DAX Commands and Tips

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

Shine_456 — Sun, 05 Feb 2023 13:26:08 GMT

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.

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

AllisonKennedy — Mon, 06 Feb 2023 01:01:20 GMT

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"

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

Shine_456 — Tue, 07 Feb 2023 04:23:26 GMT

Thanks AllisonKennedy . Your query worked.

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

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

AllisonKennedy — Wed, 08 Feb 2023 21:03:17 GMT

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"