Need help creating new column based on Category

Hi Anonymous ,

How about this:

Here the steps:

1) create a date dimension that has the columns isLast7Days, isLast30Days and isLast365Days. Below some Power Query M code that creates such a table. But you can also google other ways of doing it. It's best practice to use Date/Calendar dimensions. If you do not know, how to exactly paste the M code into the advanced editor, please check out this quick walkthrough. 

let
    // Define the start and end dates
    StartDate = #date(2024, 1, 1), // Change to your desired start date
    EndDate = DateTime.Date(DateTime.LocalNow()), // Current date

    // Generate a list of dates from StartDate to EndDate
    DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),

    // Convert the list to a table
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    // Add useful date fields
    AddYear = Table.AddColumn(DateTable, "Year", each Date.Year([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),
    AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date]), Int64.Type),
    AddWeek = Table.AddColumn(AddDay, "Week", each Date.WeekOfYear([Date]), Int64.Type),

    // Add boolean fields for last 7, 30, and 365 days
    Today = Date.From(DateTime.LocalNow()),
    AddIsLast7Days = Table.AddColumn(AddWeek, "isLast7Days", each (if [Date] >= Date.AddDays(Today, -7) and [Date] <= Today then true else false)),
    AddIsLast30Days = Table.AddColumn(AddIsLast7Days, "isLast30Days", each (if [Date] >= Date.AddDays(Today, -30) and [Date] <= Today then true else false)),
    AddIsLast365Days = Table.AddColumn(AddIsLast30Days, "isLast365Days", each (if [Date] >= Date.AddDays(Today, -365) and [Date] <= Today then true else false)),

    // Add a field for Month Name and Year-Month for better reporting
    AddMonthName = Table.AddColumn(AddIsLast365Days, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    AddYearMonth = Table.AddColumn(AddMonthName, "YearMonth", each Text.From([Year]) & "-" & Text.PadStart(Text.From([Month]), 2, "0"), type text),
    #"Changed Type" = Table.TransformColumnTypes(AddYearMonth,{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Week", Int64.Type}, {"isLast7Days", type logical}, {"isLast30Days", type logical}, {"isLast365Days", type logical}, {"MonthName", type text}, {"YearMonth", type date}})

in
    #"Changed Type"

2) Next create a relationship between the date dimension and your fact table. Make sure the connecting columns are having the same data type:

3) Next create a calculation group and add elements like the below.

7Day = CALCULATE(SELECTEDMEASURE(), 'Date'[isLast7Days] = TRUE() )

You can create calculation groups in the modelling pane:

Make sure that you are creating an explicit measure for your sales, like sales := SUM('Table'[Sales]). Using explicit measures is also best practice.

4) Lastly, add the attributes, measures and calculation groups into a matrix visual like below:

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/