How to have the months in contingency table not counted together

[Gandhary80]

Hello,

I have follow data in excel:

As you can see, there are most of data from year 2023, but 1 line ifrom 3rd of January 2024. When I create the contingency table visual, I have this result:

How to make the visual showing the data of Janury 2024 next to December (Prosinec) and not to count them together with January (Leden) 2023?

Thank you very much for your help.

[shafiz_p · Accepted solution]

Hi @Gandhary80  See the image below:

You can also sort month name by using month number column. See image below:

By using year and month name in column in matrix visual you will get your desired result.

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,

Shahariar Hafiz

[danextian]

Hi @Gandhary80 

Add a year column to you data to break down your visual by year. However, it is a best practice to use a separate dates/calendar table and relate that to your fact table, which normally has additional column for year, month, quarter, week, etc. Below is a sample M query that generates  rows of dates from Jan 1, 2020 up to today's date + 28 more days.

let
    Date_Today = Date.AddDays(Date.From(DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),8))),1),
    DateToday = Date.From(DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) + #duration(0,8,0,0))),
    Dates = let 
StartDate = #date(2020, 1, 1 ),
Count = Number.From(Date.AddDays(DateToday, 28)) - Number.From(StartDate),
Dates = List.Dates(StartDate, Count, #duration(1,0,0,0))

in Table.FromColumns({Dates}, {"Date"}),
    #"Inserted Month Name" = Table.AddColumn(Dates, "Month Short", each Text.Start(Date.MonthName([Date]), 3), type text),
    #"Inserted Month Name1" = Table.AddColumn(#"Inserted Month Name", "Month Long", each Date.MonthName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Month Name1", "Month Number", each Date.Month([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Calendar Year", each Date.Year([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Year", "FY Month Sort", each if [Month Number] >=10 then [Month Number] - 9 else [Month Number] + 3, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "FY", each "FY"  &
Text.End(Text.From( if [Month Number] >=10 then [Calendar Year] + 1 else [Calendar Year]),2), type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month & Year", each Date.ToText([Date], "MMM-yy"), type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Period", each Date.ToText([Date], "yyyyMM"), type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "IsFutureDate", each [Date] >=DateToday, type logical),
    #"Inserted Day" = Table.AddColumn(#"Added Custom4", "Day of Month", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Text.Start(Date.DayOfWeekName([Date]), 3), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Day of Week",{{"Date", type date}}),
    #"Week Starting Monday" = Table.AddColumn(#"Changed Type", "Week Starting Monday", each Date.StartOfWeek([Date], Day.Monday), type date),
    #"Week Starting Sunday" = Table.AddColumn(#"Week Starting Monday", "Week Starting Sunday", each Date.StartOfWeek([Date], Day.Sunday), type date),
    #"Week Ending Thursday" = Table.AddColumn(#"Week Starting Sunday", "Week Ending Thursday", each Date.EndOfWeek([Date], Day.Friday), type date),
    #"Week Ending Friday" = Table.AddColumn(#"Week Ending Thursday", "Week Ending Friday", each Date.EndOfWeek([Date], Day.Saturday), type date),
    #"Week Ending Saturday" = Table.AddColumn(#"Week Ending Friday", "Week Ending Saturday", each Date.EndOfWeek([Date], Day.Friday), type date),
    #"Week Ending Sunday" = Table.AddColumn(#"Week Ending Saturday", "Week Ending Sunday", each Date.EndOfWeek([Date], Day.Monday), type date)
in
    #"Week Ending Sunday"

[dharmendars007]

Hello @Gandhary80 , 

If you want the 2024 to display seprately then you need to include the year in your table visual , because currently there is no year which can filter values in your table.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN