Hi,
I have been trying to find a solution to this problem since I started Power BI 1 year ago. Please help me with the best solution for me.
I have 6 visuals and using the date table below. My x-axis is FYWW_C_PQ . I have FYWW_C_PQ column on the filter for each visuals Top N set to Top = 24 to see the last 24 weeks and including the current week. Is it possible to use Top N on the page level? If I want to see 48 weeks, I have to adjust all my visuals individually. How would you solve this issue?
I already tried creating a calculated columns
" TopN = RANKX('Calendar_MC', 'Calendar_MC'[Index WW],,DESC,Dense) ." However, Top N is still not available. Is it not available in Power BI for Top N filter in page level?
If I use Date column and use the relative date filter to calendar weeks, the problem with that is it does not include current week.
Please help me out. Thanks.
let
today = Date.From( DateTime.LocalNow() ),
start = Date.AddYears( today, -2 ),
Custom1 = List.Dates(start, Number.From( today )- Number.From( start ), #duration(1,0,0,0)),
#"Sorted Items" = List.Sort(Custom1,Order.Ascending),
#"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "FY4", each "FY"& (if Date.Month([Date])<= 6 then Number.ToText( Date.Year([Date])) else Number.ToText(Date.Year([Date])+1))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "WW", each "WW" & (if Date.WeekOfYear([Date])-27+1<=0 then Number.ToText(52+Date.WeekOfYear([Date])-27+1) else Number.ToText( Date.WeekOfYear([Date])-27+1))),
#"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Date", Order.Descending}}),
#"Inserted First Characters" = Table.AddColumn(#"Sorted Rows", "First Characters", each Text.Start([FY4], 2), type text),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Last Characters", each Text.End([FY4], 2), type text),
#"Merged Columns" = Table.CombineColumns(#"Inserted Last Characters",{"First Characters", "Last Characters"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FY"),
#"Added Conditional Column" = Table.AddColumn(#"Merged Columns", "Custom", each if [WW] = "WW1" then "WW01" else if [WW] = "WW2" then "WW02" else if [WW] = "WW3" then "WW03" else if [WW] = "WW4" then "WW04" else if [WW] = "WW5" then "WW05" else if [WW] = "WW6" then "WW06" else if [WW] = "WW7" then "WW07" else if [WW] = "WW8" then "WW08" else if [WW] = "WW9" then "WW09" else [WW]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"FY4", "WW"}),
#"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"FY", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"FYWW_C_PQ"),
#"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Date", Order.Descending}})
in
#"Sorted Rows1"
