Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Borntoreport
Helper II
Helper II

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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!

Anonymous
Not applicable

@Borntoreport, if you just need to point out the business years "2014-2015" and "2015-2016" you can use this solution with M language (that you can adjust and expand for another use with more "business years").

 

1) "Get data" and modify the data

 

2) Select the column and let the query editor autodetect the data type for you ( Transform -> Detect Data Type)

 

3) Your data type is now changed into "Whole number"

 

4) Now you can add a custom column (Add Column -> Add Custom Column) with this formula:

(. . .)

if [Column1] >= 20151001 then if [Column1] <= 20160930 then "Business Year 2015-2016" else "Other business year" else if [Column1] >= 20141001 then "Business Year 2014-2015" else "Other Business Year"

(. . .)

 

I'm not an expert but maybe is better use this formula instead of the previous solution that I gave to you.

 

Let me know if it works.

Anonymous
Not applicable

Great, happy for you! I'm also testing a version using the M language, if I reach a point I'll send you some info.

You should use a date field as the join key between your fact and date dimension in Power BI. Without the option to mark a date table in Power BI, this is the only way to guarantee that time intelligence functions work.

 

With a custom fiscal calendar, the use case for the built-in time intelligence functions loses importance, but isn't eliminated.

Anonymous
Not applicable

I was about to forget...after you have applied the formula, you can use a slicer with the calculated column as field. With this slicer it'll be easy to point out your "Business year".

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.