Determine FirstSalesWeek

[glmoon5]

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.

[v-achippa · Accepted solution]

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 · Accepted solution]

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]

@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. 

[glmoon5]

@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 · Accepted solution]

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