FiscalPeriod in Slicer

Hi @Mike,

In the current M Code I really don’t see any problem with the format where any of your column is having “Text” format data type. I’m attaching the screenshot where it defines which data type resembles which kind of format.

And since in the PBIX file as well as the M code which you’ve provided nowhere I’ve witnessed “Text.Type” format, everywhere it’s only “Int64.Type” which is whole number format.

And under the “Structure” and “Formatting” section also I don’t see any problem whatsoever. Below is the screenshot attached for the reference.

Earlier you’d mentioned that you’d to change the Text format to Whole Number format but in the given M code that you’ve provided or in the PBIX file nowhere those transformations steps are recorded where we can see that actually “Text.Type” format is converted into the “Int64.Type” format.

Thanks & Warm Regards,
Harsh

1 Like

@Harsh,
Could you also add a column to date table in query editor in text format?

Just learning here!!!]]

Thanks
Keith

Hello @Keith,

Yes, definitely you can add a Text column in your date table.

For example, let’s say you want to add a “Month Name” in your date table via M Code -

InsertMonthName = Table.AddColumn(ReferenceOfPreviousLineItem, "MonthOfYear", each Date.Month([Date]), type text)

Thanks & Warm Regards,
Harsh

Hi, @Keith and @Harsh, Thanks both for your time and I guess my question might not be clear enough. To make it easier to understand my challenge here, I have added one column as text field called “FiscalPeriodID - Text Format”. I also created a new filter using the new text field.

As you can see, the Text field only can have drop-down menu or list, but no option for Slicer. The number data format can have Slicer, but is shows some invalid FiscalPeriod number, such as 201917.

My question was if I can still use the number data format with Slicer, but not showing any invalid Fiscalperid, which is not coming from my date table at all.

Hope this helps clear any confusion.

Fiscal_Period_Sample.pbix (528.7 KB)

Thanks!

@Mike,

I would say that is your data isn’t correct that is producing 201917. View your actual data from the file. If the information in the original file is incorrect then you will have to go back to your IT area to have the source corrected.

i hope this helps.

You are also welcome.

thanks
Keith

Hi, @Keith, I don’t think there is a problem with my data. The question is in PowerBI, if the data type is number, then it will show continuous number if you use it as Slicer. My question is if there is a workaround solution?

Thanks!

Mike

Hi @Mike
I was looking at the file. data looks ok. I think its Power bi in a slicer. Right now i don’t see anything on a slicer.

i’m still looking if there is setting in slicer that isn’t correct.

keith

Hi @Mike,

All the examples I’m seeing on slicer related to financial information is using the date field within the Date table.

I don’t see any work around.
thanks
Keith

Hello @Mike and @Keith,

Firstly, I would like to confirm that there’s absolutely nothing wrong with the data so no need to worry about for that. :slightly_smiling_face:

Now the thing is since the “Date” table is being fetched from the SQL Server @Mike didn’t knew what concept is being actually applied. When I applied the M Code to derive at the same type of column at that time I understood what’s actually going with that particular column.

Below is the M Code that I’ve applied to derive the same column that @Mike have in his Date Table under the SQL Server -

let
    Source = Excel.Workbook(File.Contents("C:\Users\harjoshi1\Desktop\Dates Table.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"FiscalYear", Int64.Type}, {"FiscalPeriod", Int64.Type}, {"LastFullPeriodX", Int64.Type}, {"LastFiscalYear", Int64.Type}, {"LastFullFiscalYearX", Int64.Type}, {"FiscalPeriodID", Int64.Type}, {"FiscalPeriodID - TextFormat", Int64.Type}}) , 
    #"InsertFiscalPeriodID" = Table.AddColumn(#"Changed Type" , "FiscalPeriodID1" , each [FiscalYear] * 100 + [FiscalPeriod] )
in
    InsertFiscalPeriodID   

Now, if you observe the last line item of code InsertFiscalPeriodID it actually is “Fiscal Year * 100” and then it’s “+ FiscalPeriod” so actually the number derived from that will be “202012” which means that Year will be 2020 and Month will be December, upto this everything is correct but from now on when we drag the slicer further it will keep on adding the numbers until it reaches 100 for that particular year and then again it will start for the next year. “This Problem will be there because the data/column type is converted into the Whole Number or as per M Language you can Int64.Type

But now when you convert this same column into the “Text.Type” format it will only the values upto a particular month and a particular year i.e. when your year 2019 changes to the year 2020 the value will change from 201912 to 202001 and not to the 201913 due to the change in format.

Now, since the format of the FiscalPeriodID is changed to the Text Format ultimately it won’t be possible to have “Between” format slicer which is only and only available in the case of “Number Format”.

Please Note: This type of columns are kept in the date table for sorting purposes instead you can create other columns such as “Month Name”, “Month Number of a particular month”, “Quarters” and many other columns to analyze your data or lastly if you want to proceed further with this type of column then you’ll by default will be required to keep your slicer either in List or Dropdown format. There is absolutely no other alternative for this.

I’m attaching the Excel file of my Date Table as well as the PBIX file of my working for the reference.

Hoping you find this useful and helps you to understand the concept running behind the “FiscalPeriodID” column. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

Dates Table.xlsx (59.1 KB)

Fiscal Period - Slicer.pbix (778.4 KB)

Attaching the my working file.

Fiscal_Period_Sample (1).pbix (532.3 KB)

1 Like

Hi @Harsh,

Thanks for explaining what is happening
Keith

@Keith @Harsh

Hi, Guys, really appreciate all the time and efforts you both put into this question and tried to dig out what the issue was. @Harsh, your explanation on the M code behind the scene helps a lot. Now I know what the limitation is in the tool when coming to this kind of scenario.

Thanks to both of you again!

Mike

You are welcome, I’m learning too :slight_smile:

thanks
Keith

Hi @Mike. No workaround that I’m aware of. You can only get a slider with a continuous series of dates or whole numbers, AFAIK. Greg

Hello @Mike and @Keith,

You’re welcome. :slightly_smiling_face:

I’m glad I was able to help you guys.

Thanks & Warm Regards,
Harsh

thank you @Harsh. I’m learning too :slight_smile:

Keith