Forum Discussion

nicolasvc's avatar
nicolasvc
Helper III
4 years ago
Solved

group data by minimum value

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.

storeyearmonthweeksales
12021112000
22021111000
12021121300
2202112300
...............
220211253900

 

storeyearmonthweeksales
12021112000
22021111000
12021251000
...............

 

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?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      nicolasvc
      Helper III
      4 years ago

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

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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

    • nicolasvc's avatar
      nicolasvc
      Helper III
      4 years ago

      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.

    • nicolasvc's avatar
      nicolasvc
      Helper III
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.