Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, still learning here and I have set up a date table for fiscal years, fiscal month, fiscal weeks. My problem is that I have a matrix visualization and for fiscal year 2017, it leaves Nov and Dec blank. But for year 2018 thru 2020, it Nov & Dec have values.
My dax formula is below.
let
StartDate = #date(2016,1,1),
YearsToGenerate=10,
FiscalYearStartMonth=11,
BaseList=List.Dates(
StartDate,
YearsToGenerate*366,
#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(BaseList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Week of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Month Name",{"Date", "Year", "Quarter", "Month", "Month Name", "Week of Year"}),
#"Inserted Day" = Table.AddColumn(#"Reordered Columns", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Day of Week",{"Day of Week"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Fiscal Date", each Date.AddMonths([Date],13-FiscalYearStartMonth)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Fiscal Date", type date}}),
#"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([Fiscal Date]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
#"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([Fiscal Date]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([Fiscal Date]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
#"Inserted Week of Year1" = Table.AddColumn(#"Renamed Columns3", "Week of Year.1", each Date.WeekOfYear([Fiscal Date]), Int64.Type),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Week of Year1",{{"Week of Year.1", "Fiscal Week"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns4",{"Fiscal Date"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each true)
in
#"Filtered Rows"
Any pointers would be welcome.
Marty
Hi @mgradijan ,
In your post, I just see your date table, it show well in table or matrix. As @lbendlin mentioned, you need to check whether there exist fact data in 2017-11 or 2017-12. In addition, I can't use only date table to reproduce your design, so if possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will test this in my environment.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the input everyone. Yes my data only goes back to January 2017 so that explains the missing gaps.
A new problem related to the fiscal year calendar is that I now have my fiscal months set up beginning Nov,Dec,Jan...etc to end in Oct. but when I drag over the sales value over to the table, it is summing Nov and December as part of the previous year, then January the sum of the sales does not include the Nov and Dec sales.
help!?
Thanks
- this is a Power Query formula, not a DAX formula
- it works as designed(*) and has no gaps. You need to mark it as a date table in Power BI.
- probably your missing months are the result of missing facts. How did you link the facts table to this dates table?
*) Personally I think that dates tables (especially for Fiscal year situations like yours) need to be externally sourced.
Note: Yes, dates tables need to "cover" all facts dates for date math. But not much more than that! One year prior to what your facts have is enough.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.