Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to Sum values for each Week based on it's week sequence

Hi everyone,

I need help to create 4 measures, one measure to calculate the sum of sales based on the week,

For example,

I want one measure to calculate the sum of sales for the first week of the current month filtered, then another measure to calculate the sum of sales for the second week, another for the third, and a last one for the fourth week,

I need these measures to be flexible because my month column will be changing regularly, 

 


For example, using my table above as a reference, I would like to get a measure showing a total of (28+58=86) for the first week, which is "18", when I'm seeing the Month "Mai", then a second measure showing a total of 27, because the second week is "19".

I've tried to use the formula "MIN", I've got for the first week, but for the week onwards, if I sum a "+1" at my "MIN" to get the second-week result for example, Power BI doesn't recognize my command. 

Thank you for your help.

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    try like:

    measure1 =
    VAR MinWeek = MINX(ALLSELECTED(data), data[Week])
    RETURN
    MINX(
    FILTER(
    data,
    data[week]=MinWeek
    ),
    data[Sales]
    )

    measure2 =
    VAR MinWeek = MINX(ALLSELECTED(data), data[Week])
    RETURN
    MINX(
    FILTER(
    data,
    data[week]=MinWeek+1
    ),
    data[Sales]
    )

    ...

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi FreemanZ ,

      The "+1" into the MinWeek doesn't work here, 
      The first measure is working just fine,