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.
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.
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)