topic how to transform month and years as numbers to exploitable periods in DAX Commands and Tips

how to transform month and years as numbers to exploitable periods

debutant — Sat, 15 Oct 2022 20:21:06 GMT

Hello,

I have data with year and month in separate columns in number format, I would like to exploit those rather than the invoicing date field (for sales cutoff consideration).

How can this be achieved ?

I have tried a solution I found suggested in this topice Solved: Converting from Month Name and Year to Date - Microsoft Power BI Community which is to create these 2 additional columns:

MonthNum = SWITCH([Month],"January",1,"February",2,"March",3)

Date = EOMONTH(DATE([Year],[MonthNum],1),0)

But I have the following result

So I have changed my year and month columns from number to text format but that didn't make adifference.

Can you please help ?

thank you

Re: how to transform month and years as numbers to exploitable periods

Anonymous — Sat, 15 Oct 2022 20:33:23 GMT

I think the most simple is to create a date table with date, month year etc..; and link it to your fact table

Re: how to transform month and years as numbers to exploitable periods

v-yueyunzh-msft — Mon, 17 Oct 2022 02:05:30 GMT

Hi , debutant

According to your dax , i think there is no problem.

This is my test dax:

Column = EOMONTH( DATE([Year],[Month],1) , 0)

The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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