group data by minimum value

[nicolasvc]

I have a table the id of the store, year, month, the number of the week in the year, and the corresponding sales, but I need to have only the first week of the month per store.

store	year	month	week	sales
1	2021	1	1	2000
2	2021	1	1	1000
1	2021	1	2	1300
2	2021	1	2	300
...	...	...	...	...
2	2021	12	53	900

store	year	month	week	sales
1	2021	1	1	2000
2	2021	1	1	1000
1	2021	2	5	1000
...	...	...	...	...

This I could do in Power Query, but now I have modified columns in DAX and I need to do it in DAX. For this I tried to group by the minimum value of the week, but it returns all the same. The function I have is this:

Table 2 = SUMMARIZE ('Table 1', 'Table 1' [store], 'Table 1' [year], 'Table 1' [month], 'Table 1' [sales], ' Table, "week", MINX (CURRENTGROUP (), 'Table 1' [week]))

Is there any way to do it with another function, or am I misusing it?

[v-rzhou-msft · Accepted solution]

Hi @nicolasvc 

I think you want to create a calcualte table to show frist week of each month based on "Table 1".

Try my code.

Table 2 =
VAR _T1 =
    SUMMARIZE (
        'Table 1',
        'Table 1'[Store],
        'Table 1'[Year],
        'Table 1'[Month],
        "MinWeekEachMonth",
            CALCULATE (
                MIN ( 'Table 1'[Week] ),
                FILTER (
                    'Table 1',
                    AND (
                        'Table 1'[Year] = EARLIER ( 'Table 1'[Year] ),
                        'Table 1'[Month] = EARLIER ( 'Table 1'[Month] )
                    )
                )
            )
    )
VAR _T2 =
    ADDCOLUMNS (
        _T1,
        "Sales",
            CALCULATE (
                SUM ( 'Table 1'[Sales] ),
                FILTER (
                    'Table 1',
                    'Table 1'[Store] = EARLIER ( [Store] )
                        && 'Table 1'[Year] = EARLIER ( [Year] )
                        && 'Table 1'[Month] = EARLIER ( [Month] )
                        && 'Table 1'[Week] = EARLIER ( [MinWeekEachMonth] )
                )
            )
    )
RETURN
    _T2

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@nicolasvc sounds good as far as it works. I have to spend time figuring out doing the DAX way.

[parry2k]

@nicolasvc I see, no issue, we can still work on it, so you want to get first week record of each month.

[nicolasvc]

I made a patch solution, grouped the week into power query and then use lookupvalue to match between the columns.

[nicolasvc]

Exactly, in power query I could do it by grouping the columns I wanted and using the MIN function in the week. I tried to do something similar in DAX and it didn't work.

[parry2k]

@nicolasvc try this, assuming every month has a week 1 record.

Table 2 = 
FILTER( 'Table 1', 'Table 1' [week] = 1 )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[nicolasvc]

I forgot to specify that the week corresponds to the number of the week of the year 😞