Power BI DAX Measure to create a view of Weekly Sales Count

[curious_monkey]

Hi Guys, need help in writing a DAX query to create a Table view in Power BI

As you can see above, I have a Table - which has cities and dates with Sales count, I want to create a view with single record per city and sales count of all its occurrences in that week ending on Saturday.

So far i have written this ('Data Sheet' is the name of the excel I am reading data from ...)

WeeklySalesCount = 
ADDCOLUMNS(
    GENERATE(
        DataSheet,
        DATESBETWEEN(
            CALENDAR(MIN(DataSheet[Date]), MAX(DataSheet[Date])),
            [Date] - WEEKDAY([Date], 2) + 1,
            [Date] - WEEKDAY([Date], 2) + 7
        )
    ),
    "Location", InvIssue[Location],
    "Sales", InvIssue[Sales]
)

but power gives me error saying, it cannot find this column date: Any other idea, as how to create this table using a DAX measure???

[ThxAlot]

Bizarre that your 1 Apr, 2023 is Sunday ...

Som Weekly.pbix

Anyway, it's easy enough to solve by PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddLLDcMgDAbgXThHKjamwLEpW0TZf42mtPIjhhv6ZP2yjY8j7GEL8Ij0wIjpeqcUzu0I/cYIg7/VqKvblBHn/Kt+XU/SHAe/71y4OiumyNl53rdmTJzddDVxNkTtRaaPfs7hZi2Zm4Hkc7wTt2P8v4Lu6rM4+WlHjhm3LjzJvE/vu/MmOcZJ+pnUj/zi++l3T/K3ULXDwuVErJeFqyuui7mav7XuPHM/aO6hLlzyjav9I8znNa7+BSf31p1f+zk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Loc = _t, Date = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Loc", type text}, {"Date", type date}, {"Qty", Int64.Type}}),
    Sorted = Table.Sort(#"Changed Type",{{"Loc", Order.Ascending}, {"Date", Order.Ascending}}),

    #"Grouped by Week" = Table.Group(Sorted, {"Loc","Date"}, {"Grp", each [Weekly = List.Sum([Qty]), Saturday = Date.ToText(Date.EndOfWeek([Date]{0},Day.Sunday), "ddd dd/MM/yyyy")]}, 0, (x,y) => Byte.From(Date.WeekOfYear(x[Date],Day.Sunday)<>Date.WeekOfYear(y[Date],Day.Sunday))),
    #"Expanded Grp" = Table.RemoveColumns(Table.ExpandRecordColumn(#"Grouped by Week", "Grp", {"Saturday","Weekly"}), "Date")
in
    #"Expanded Grp"

Equally easy by DAX

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

New Table = 
VAR _wknumber =
    ADDCOLUMNS ( Data, "@wknumber", WEEKNUM ( Data[Date], 1 ) )
VAR _wkendingdate =
    ADDCOLUMNS (
        _wknumber,
        "@wkendingdate", MAXX ( FILTER ( _wknumber, [@wknumber] = EARLIER ( [@wknumber] ) ), Data[Date] )
    )
RETURN
    GROUPBY (
        _wkendingdate,
        Data[Location],
        [@wkendingdate],
        "@Sales", SUMX ( CURRENTGROUP (), Data[Sales] )
    )