Forum Discussion

glmoon5's avatar
glmoon5
Helper I
1 year ago
Solved

Determine FirstSalesWeek

Hi,

 

I have 2 measures that I created:

 

FirstSaleWeek

LastSaleWeek

 

I have UPC ID as the product I want to track sales on.

 

My LastSaleWeek is correctly returning the last sales week however the first sales week is returning 1/11/2025 even though my sales data goes back into 2024.

 

Here is the FirstSaleWeek that is the issue.

 

FirstSaleWeek =
VAR _currentItem = MAX('ACI Distribution'[UPC ID])  // Use the item identifier column
VAR _firstWeek =
    CALCULATE(
        MIN('ACI Distribution'[W/E Date]),  // Use the calculated week number column
        FILTER(
            ALLSELECTED('ACI Distribution'),  // Use ALLSELECTED to avoid filtering by the visual's context
            'ACI Distribution'[UPC ID] = _currentItem
        )
    )
RETURN
    _firstWeek
 
And here is the LastSaleWeek which is returning expected results.
 
LastSaleWeek =
VAR _currentItem = MAX('ACI Distribution'[UPC ID])  // Use the item identifier column
VAR _lastWeek =
    CALCULATE(
        MAX('ACI Distribution'[W/E Date]),  // Use the calculated week number column
        FILTER(
            ALLSELECTED('ACI Distribution'),  // Use ALLSELECTED to avoid filtering by the visual's context
            'ACI Distribution'[UPC ID] = _currentItem
        )
    )
RETURN
    _lastWeek
 
Ay help would be greatly appreciated.
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi glmoon5,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Based on the current measure, the issue here is with the use of ALLSELECTED. To fix this, please update the FirstSaleWeek measure to use ALL() instead of ALLSELECTED() like this:

     

    FirstSaleWeek =
    VAR _currentItem = MAX('ACI Distribution'[UPC ID])
    VAR _firstWeek =
        CALCULATE(
            MIN('ACI Distribution'[W/E Date]),
            FILTER(
                ALL('ACI Distribution'),
                'ACI Distribution'[UPC ID] = _currentItem
            )
        )
    RETURN
        _firstWeek

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi glmoon5,

     

    Thank you for the response and confirming that the issue is resolved now. Power bi does not allow measures to be used directly in slicers. Slicers only work with columns not measures.

    To enable slicing by First sale week, you can create a calculated column like this:

     

    FirstSaleWeek_Column =

    CALCULATE(

        MIN('ACI Distribution'[W/E Date]),

        FILTER(

            'ACI Distribution',

            'ACI Distribution'[UPC ID] = EARLIER('ACI Distribution'[UPC ID])

        )

    )

     

    you can add this to a slicer.

     

    Thanks and regards,

    Anjan Kumar Chippa

4 Replies

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi glmoon5,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Based on the current measure, the issue here is with the use of ALLSELECTED. To fix this, please update the FirstSaleWeek measure to use ALL() instead of ALLSELECTED() like this:

     

    FirstSaleWeek =
    VAR _currentItem = MAX('ACI Distribution'[UPC ID])
    VAR _firstWeek =
        CALCULATE(
            MIN('ACI Distribution'[W/E Date]),
            FILTER(
                ALL('ACI Distribution'),
                'ACI Distribution'[UPC ID] = _currentItem
            )
        )
    RETURN
        _firstWeek

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

    • glmoon5's avatar
      glmoon5
      Helper I
      1 year ago

      v-achippa 

       

      Thank you that fixed my issue. Now how can I use FirstSaleWeek as a slicer. It won't let me add the field to the slicer. 

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi glmoon5,

         

        Thank you for the response and confirming that the issue is resolved now. Power bi does not allow measures to be used directly in slicers. Slicers only work with columns not measures.

        To enable slicing by First sale week, you can create a calculated column like this:

         

        FirstSaleWeek_Column =

        CALCULATE(

            MIN('ACI Distribution'[W/E Date]),

            FILTER(

                'ACI Distribution',

                'ACI Distribution'[UPC ID] = EARLIER('ACI Distribution'[UPC ID])

            )

        )

         

        you can add this to a slicer.

         

        Thanks and regards,

        Anjan Kumar Chippa

    • glmoon5's avatar
      glmoon5
      Helper I
      1 year ago

      v-achippa 

       

      Thank you that fixed my issue. Now how can I use FirstSaleWeek as a slicer. It won't let me add the field to the slicer.