topic Re: DAX column to get new date column based on existing date column in DAX Commands and Tips

DAX column to get new date column based on existing date column

datpbi_newbie — Fri, 04 Feb 2022 03:47:54 GMT

Hi I am very new to Power BI and coming from SQL background.

I would like to create a new Matrix Object with Metric_Name, Target, and Month_Year dimensions and No of Requests as Fact.

The Metric_Name is having values like "Metric 1', 'Current', 'Closed', 'onHold' and column values partially exist in one of the columns in the model.

Metric_Name --> Status column (Current , Closed, onHold), Metric 1 --> if(Dummy = 'True', Metric1)

Target --> This column not in the model and values are statis as per Metric Name - Current - 5, Closed - 10, onHold - 3, Metric1 - 1

For the Month_Year dimension, I need to limit the data latest 3 months meaning if the Max Request date is Jan 20 2022, I need to show Jan 2022, Dec 2021, and Nov 2021 in the Matrix table object. I have a Request_Date in the model.

How to create these in DAX measure?

Also, how to get started with DAX and share any good material to get started

No of Requests- Count of request id

Re: DAX column to get new date column based on existing date column

ValtteriN — Fri, 04 Feb 2022 06:22:54 GMT

Hi,

Since you have the data for the rest you only need to add measure for target. For that try something like this: target =
SWITCH(MAX(Table[Metric Name]),"Current",5
"Closed",10,
"onHold",3,
"Mteric1",1)

You can get the last 3 months by placing your date column as a filter on your visual:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: DAX column to get new date column based on existing date column

datpbi_newbie — Fri, 04 Feb 2022 12:18:13 GMT

Thanks for the reply.

Is relative date option use Start Of the Month? Also, I need to calculate last 3 months based max date in the request_date. The current max date is Jan-20 and today date is Feb 4th. So the chart should show only Nov-01-21 to Jan-20-22 only?

Re: DAX column to get new date column based on existing date column

datpbi_newbie — Fri, 04 Feb 2022 12:22:43 GMT

The Metric_Name column didn't exist in the model. The values in that column come from one more column condition.

The first 3 values coming from "Status" column and last value is logical cond on "Dummy" column

Metric_Name --> Status Column ( Current , Closed, onHold ) and 'Metric1' ---> if(Dummy=true, 1, 0) for measure.

So how to write this new dynamic Metric_Name in the DAX?

Re: DAX column to get new date column based on existing date column

v-zhangti — Wed, 09 Feb 2022 07:30:37 GMT

Hi, datpbi_newbie

Regarding limiting the last 3 months of data, you can try the following methods.

Measure = VAR _Startdate = SELECTEDVALUE('Table'[Request_Date])-90 VAR _Enddate = SELECTEDVALUE('Table'[Request_Date]) RETURN IF ( SELECTEDVALUE('Date'[Date]) >= _Startdate && SELECTEDVALUE('Date'[Date]) <= _Enddate, 1, 0 )

Put Measure into Filters, set to equal 1.

When the request date is selected data from the last 90 days will appear.

Is this the result you expect? Regarding the other requirements you mentioned, can you provide images or simple PBIX files about the source data for testing? And show the output you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.