Hey, I have a quick question for using FiscalPeriod in Slicer. I have a field called FIscalPeriod in my Date table. I wanted to use Slicer on PowerBI. The original data type for FiscalPeriod is text, which did not allow me to use the Slicer in PowerBI, so I changed the data type to Whole number, which was fine at the first look. But when I moved the slicer, I saw FiscalPeriod 201920. Is it possible to find a way to limit to the value to the range of the real FiscalPeriod value?
Thanks!
Hello @Mike,
Thank you for posting your query onto the Forum.
Just wanted to ask have you tried the extended date table created by @Melissa which is available under the M Code Showcase.
Now coming back to your question you can add a calculated column in your Date table by using the formula given below -
Fiscal Year = IF( Dates[Month Of Year] <= 6, Dates[Year] , Dates[Year] + 1 )
Now you can drag this newly created column in your slicer.
Please Note: The Month of Year represents the number of month i.e. January = 1 and not January in text format.
Hoping you find this useful. 
Thanks & Warm Regards,
Harsh
Hi, @Harsh, thank you very much. I have my own fiscal year table in our system, so I am king of stuck to the table I have. as you can see below, my fiscal period for 2019 ends with 201912. But when I grab this field to my slicer, it shows value greater than 201912, which I believe it is due to the data type for FiscalperiodID is whole number? How can I limit the fiscal period to a valid number (such as 201910, 201912, not 201920)?
Thanks!
Mike
Hi @Mike,
I sound like your date query isn’t set up properly or does your company fiscal year-end ends on Jan 31?
Another possibility is year-end adjustment that happens in January, if that happens then it would be part of December 2019.
My gut is saying to me that the query isn’t set up correctly when you created the date table.
Its just a thought.
thanks
Keith
Hi @Mike. When you use a whole number data type in a slicer like the one you’ve shown with a slider, you get all integers between your first and last entry, hence 201920. Use type text, perhaps with a normal list or dropdown slicer. Greg
Hi, @Greg, that was what I thought. I really want to take advantage of the Slicer function, however, Type Text does not allow me to do so. Do you know if there is a way around? Thanks!
Mike
Hi @Mike,
Can you please send the Date code or PBIX file that you’re using so that we can have a look where the problem actually lies?
Thanks & Warm Regards,
Harsh
I can see there is an issue with some of the data (highlighted) is for jan 2020 has fiscal 2019 which doesn’t make sense either. Feb 2020 has month 1 which to me is start of a new fiscal year. (doesn’t make sense unless the fiscal year starts in feb 1.)
thanks
keith
Hi, Keith, this year, our fiscal year start from Feb 1st, 2020. I re-sort the table and it gives a better view.
Hi, @Harsh Harsh, attached you will see the sample file, on the visual, when you move the slicer, you will see the fiscal period grater than 12, such as 201920.
I guess it is due the data type for fiscalperiodID (as whole number), but would like to know if there is a workaround.
Thanks!
Mike
Fiscal_Period_Sample.pbix (527.8 KB)
Hi, @Keith, thanks for your suggestion, it is always great to know others’ suggestion and I truly appreciate your help.
Mike
Hello @Mike,
The re-sort that you’ve made in your date is not correct. Because if your Fiscal Year starts from 1st Feb then it should also have an retrospective effect in your date table which I don’t see. Below are the screenshots provided for the reference -
In the above screenshots, you’ll observe that your Fiscal Year for 2018 starts from 3rd Feb and ends on 1st Feb whereas it should have been 1st Feb and 31st Jan respectively.
Alternatively, for the year 2019 and 2020 also, there’s the same problem. I’m attaching the screenshot below for the year 2020 where your Fiscal Period although starts from 1st Feb but ends 2 days before the actual end of the Fiscal Year.
Also upon checking the format of the FiscalPeriodID in your file I absolutely don’t see any type of problem where it’s been converted into the text because it’s having “Int64.Type” format so it’s already been there in the number format.
Still I’m curious to know if it’s possible for you to change your date table to the one which is already available on the Forum and this is the compact and the concise date table that one can have in their report.
Thanks & Warm Regards,
Harsh
Hi @Harsh,
Mike stated that their company fiscal year starts in February. I asked the question earlier just to make sure to see if the query wasn’t setup correctly.
@Mike, you might want to check your date query to ensure that it is working correctly?
Keith
Hi, @Harsh and @Keith our fiscal year starts from the first weekend of Feb, so the start of each fiscal year is different depending on the start of first week of Feb.
Converting to Text is not problem, the problem is the filter will not have the slicer option when the data type is Text, which is the place I struggled with.
Thanks!
Mike
Hi @Mike,
The file that you’d attached in that I didn’t witnessed the Text data type anywhere in your date table.
Can you please provide the “Date” table code from the Advanced Editor and paste it here? so that we can check where you’re facing a problem.
If there’s any problem in your M code then for that we also have an expert who can have a look and fix your date table.
Thanks & Warm Regards,
Harsh
Hi @Mike,
I think it might be the query code that creates the date table might needs to be changed to reflect what you need. I think that where @Harsh is coming from.
I come from a financial background , usually the fiscal year starts the first day of the month not the first weekend in month. (i never seen it before). That is why i question about the fiscal year start.
Thanks
Keith
Hi, @Harsh, here is the M code from my date table.
let
Source = Excel.Workbook(File.Contents(“filepath\Sample_data.xlsx”), null, true),
Date_Sheet = Source{[Item=“Date”,Kind=“Sheet”]}[Data],
#“Promoted Headers” = Table.PromoteHeaders(Date_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}})
in
#“Changed Type”
Right now, the FiscalPeriodID is Int64 type.
The Date table comes from our SQL server, so we have no option to switch to other date table, but would like to know any solution to make Slicer work, otherwise, we can live with the drop-down menu with Text Type.
Thanks!
Mike
