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/

Thank you tackytechtom that worked perfect. I need your help again with adding couple more columns from a different table. Below is an example of the table

Table2:

Above are daily goals for those items. What I need to do is I need to first calculate goals for 7D, 30D, and 365D and bring them in along with the other columns so they are side by side and then calculate the different. See below the for the end result. Is this possible in PBI? Please help!!