Forum Discussion
Group values
Hello,
i have converted my dates into numbers like 20160122. In my company the business year starts in october. How can i group the converted dates with a new columns like value of column A between 20151001 and 20160930 equals "Business Year 2015/16" in column B. The same for "Business Year 2014/15" which equals 20141001 and 20150930. I am new with Power BI and starting to learn the DAX-Functions. Do you have any recommendations?
In general I have the problem that Power BI does not convert my date data in the right way. I have tried to convert them from text fields into date fields, but the results are always errors.
Sorry Borntoreport,
I skipped your question about the conversion. To solve it you have to:
1) Split the column by number of characters ( Transform -> Split columns -> By number of characters) with:
- "4" as Number of character (to indicate the year)
- "Once, as far left as possible" as Split
2) Re-split the right column by number of characters with:
- 2 as number of character (to separate the month from the day)
- "Once, as far left as possible" as Split
3) After that you have three columns (year-month-day)
4) All you have to do is merge the columns (Add column -> Merge columns) with a custom separator/tab/space (as you want)
5) Finally you select the column and change the data type into date.
I remind to you that I'm not an expert, certainly there is a DAX formula to do it.
7 Replies
Sorry Borntoreport,
I skipped your question about the conversion. To solve it you have to:
1) Split the column by number of characters ( Transform -> Split columns -> By number of characters) with:
- "4" as Number of character (to indicate the year)
- "Once, as far left as possible" as Split
2) Re-split the right column by number of characters with:
- 2 as number of character (to separate the month from the day)
- "Once, as far left as possible" as Split
3) After that you have three columns (year-month-day)
4) All you have to do is merge the columns (Add column -> Merge columns) with a custom separator/tab/space (as you want)
5) Finally you select the column and change the data type into date.
I remind to you that I'm not an expert, certainly there is a DAX formula to do it.
Hey B129,
thank you for your fast response! I have tried it already and it works!
Hi Borntoreport,
I'm not an expert, but try with a calculated column like:
IF(AND(Data[Column1] >= 20151001;Data[Column1] <= 20160930); "Business Year 2015/16";IF(AND(Data[Column1] >= 20141001;Data[Column1] <= 20150930); "Business Year 2014/15") ........... )
you can expand the formula with all the year you want to analize.
Let me know if it works.
