Need help creating new column based on Category

[MilindG]

Hello all, 

Very new to PowerBI and need help creating new columns based on couple criteria. Below is what my data looks like

Site	Date	Type	Sales
2	10/1/2024	Coke	50
1	09/30/2024	Apple	50
2	9/29/2024	Apple	125
1	9/29/2024	Coke	100
1	9/15/2024	Pepsi	500
2	9/1/2024	Pepsi	75
2	7/5/2024	Coke	75
1	6/30/2024	Apple	150
1	9/27/2024	Pepsi	200
2	9/30/2024	Pepsi	100
2	7/28/2024	Apple	225

What I need is I need sum of Column by type for last 7 days, last 30 days and last 365 days by site. Below is what my end goal should look like. Please help!!

Site	Coke	Pepsi	Apple
1
7Day	100	200	50
30Day	100	700	50
365Day	100	700	200
2
7Day	50	100	125
30Day	50	175	125
365Day	125	75	350

[MilindG]

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:

Site	Apple_goal	Coke_goal	Pepsi_goal
1	2	1	2
2	3	.5	1.5

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!!

Site	Coke	Coke_Goal	Diff	Pepsi	Pepsi_Goal	Diff	Apple	Apple_Goal	Diff
1
7Day	100	7	93	200	14	186	50	14	36
30Day	100	30	70	700	60	640	50	60	-10
365Day	100	365	-265	700	730	-30	200	730	-530
2
7Day	50	3.5	46.5	100	10.5	89.5	125	21	104
30Day	50	15	35	100	90	10	125	90	35
365Day	125	182.5	-57.5	175	547.5	-372.5	350	1095	-745

[tackytechtom]

Hi @MilindG ,

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/

[MilindG]

Hello @tackytechtom  Thank you so much for getting back. I'm stuck at step 4 where its giving me an error due to table can't determine the relationship between two or more fields. 

I ensured both the datatype are same however the date column in my initial table obviously have same dates for different sites and its causing the relationship to me 'One to Many' Can you please help solving this issue or if you can share your example dashboard would be greatly appreciated. 

[tackytechtom]

Hi @MilindG ,

The relationship one to many (date -> fact table) is correct. I need to recreate the report. I'll come back to you tonight.

/Tom

[MilindG]

@tackytechtom Sounds good. Thank you!!

[tackytechtom · Accepted solution]

Hi @MilindG ,

Find attached the file. I added two rows to the mockup data with the same date to see whether that causes issues. It still worked.

Let me know, how it goes 🙂

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