Adding a season flag to products with calculated column

[hidenseek9]

I have daily sales file with dates, products and sales amount. 

I want to create a gantt chart to visually capture what products were available from what date to what date. 

In the data, I have a seasonal product that are only sold in specific season with the same product name. 

Hence, I want to flag season ID to products using below calculated columns. 

First I created this calculated column to capture the gap between the last sales and the next sales to flag gap between sales date. 

Sales Gap Flag =

VAR PrevDate =

        CALCULATE(

            MAX('Daily Sales'[Date]),

            FILTER(

                'Daily Sales',

                'Daily Sales'[Product Name] = EARLIER('Daily Sales'[Product Name]) &&

                'Daily Sales'[Date] < EARLIER('Daily Sales'[Date])

            )

        )

VAR GapDays = DATEDIFF(PrevDate,'Daily Sales'[Date],DAY)

VAR SalesGapFlag = IF(ISBLANK(PrevDate) || GapDays > 10,1,0)

RETURN SalesGapFlag

Then I want to create another calculated column to provide season ID to products. 

Meaning, from the first sales gap flag =1 to the next 1, season ID to flag 0 in season ID and from the second "1" to the third "1", season ID to flag 1 in season ID so on and so forth. 

Hence season ID is giving a number of seasons that the products are sold. 

However, below calculated column is NOT working. 

Season ID =

VAR SalesDate = 'Daily Sales'[Date]

VAR SKUName = 'Daily Sales'[Material Description]

VAR SeasonID =

RANKX(

    FILTER(

        'Daily Sales',

        'Daily Sales'[Material Description] = SKUName &&

        'Daily Sales'[Sales Gap Flag] = 1

    ),

    'Daily Sales'[Date],

    ,

    ASC,

    Dense

)

RETURN SeasonID

In the first screenshot, 2024/11/16 is when a new season begins, but season ID is showing 3, not 4. 

In the second screenshot, 2024/03/15 is when a new season begins, but season ID is showing 2, not 3. 

Also when there is a gap in sales date, it is assigning new season ID, which is incorrect. 

How can I fix this issue?

[Elena_Kalina · Accepted solution]

Hi @hidenseek9 

Please try this one

Season ID =
VAR CurrentProduct = 'Daily Sales'[Product Name]
VAR CurrentDate = 'Daily Sales'[Date]
VAR GapFlagDates = 
    FILTER(
        'Daily Sales',
        'Daily Sales'[Product Name] = CurrentProduct &&
        'Daily Sales'[Sales Gap Flag] = 1 &&
        'Daily Sales'[Date] <= CurrentDate
    )
RETURN
    COUNTROWS(GapFlagDates) - 1  // Subtract 1 to start Season ID at 0

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! 

Thank you.

[Elena_Kalina · Accepted solution]

Hi @hidenseek9 

Please try this one

Season ID =
VAR CurrentProduct = 'Daily Sales'[Product Name]
VAR CurrentDate = 'Daily Sales'[Date]
VAR GapFlagDates = 
    FILTER(
        'Daily Sales',
        'Daily Sales'[Product Name] = CurrentProduct &&
        'Daily Sales'[Sales Gap Flag] = 1 &&
        'Daily Sales'[Date] <= CurrentDate
    )
RETURN
    COUNTROWS(GapFlagDates) - 1  // Subtract 1 to start Season ID at 0

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! 

Thank you.

[hidenseek9]

@Elena_Kalina 

Thank you for this! This works perfectly!

One last question is when using ghant chart, and put these calculated columns under start date and end date, it only takes either the earlist season or the latest season. It does not take in all seasons that products are sold in the gantt chart. 

Do I need to create measures to achieve what I want?

Launch Date = 

CALCULATE(

    MIN(SalesData[Date]),

    ALLEXCEPT(SalesData, SalesData[ProductName], SalesData[Season ID])

)

Discontinued Date = 

CALCULATE(

    MAX(SalesData[Date]),

    ALLEXCEPT(SalesData, SalesData[ProductName], SalesData[Season ID])

)

I tried these measures, but did not work properly. 

These measures actually returned the oldest date in the data and today's date and did not work at all. 

Much appreciate your support! 

[Elena_Kalina]

Hi @hidenseek9 

The issue with using ALLEXCEPT is that while it preserves filter context for specified columns, it may conflict with other filters in your report. SELECTEDVALUE provides more precise control by capturing the current filter context explicitly.

Here are the corrected measures:

Launch Date = 
VAR CurrentProduct = SELECTEDVALUE('Daily Sales'[Product Name])
VAR CurrentSeason = SELECTEDVALUE('Daily Sales'[Season ID])
RETURN
CALCULATE(
    MIN('Daily Sales'[Date]),
    FILTER(
        ALL('Daily Sales'),
        'Daily Sales'[Product Name] = CurrentProduct &&
        'Daily Sales'[Season ID] = CurrentSeason
    )
)

Discontinued Date = 
VAR CurrentProduct = SELECTEDVALUE('Daily Sales'[Product Name])
VAR CurrentSeason = SELECTEDVALUE('Daily Sales'[Season ID])
RETURN
CALCULATE(
    MAX('Daily Sales'[Date]),
    FILTER(
        ALL('Daily Sales'),
        'Daily Sales'[Product Name] = CurrentProduct &&
        'Daily Sales'[Season ID] = CurrentSeason
    )
)

Additionally, I would recommend:

1. Create a composite key for unique season identification by adding this calculated column:

Product Season Key = 
'Daily Sales'[Product Name] & "|" & 'Daily Sales'[Season ID]

1. In visual I would add "Product Season Key" to the Legend field, use the "Launch Date" and "Discontinued Date" measures for their respective fields and add  "Product Name" to the Category field

[hidenseek9]

@Elena_Kalina 

This is amazing and gantt chart is working, but is there a way to show the same product under the same row, instead of using additional rows for additional seasons? 

The visual gets busy with too many rows now. 

[Elena_Kalina]

@hidenseek9 

Let’s try the following approach:

1. Remove "Product Season Key" from Legend.

2. Keep only "Product Name" in the Rows/Category field.

3. Add "Season ID" to either the Color Saturation or Legend field.

This will consolidate all seasons of the same product into a single row while still visually differentiating them by color.

If my suggestions were helpful, don’t forget to give a "Kudos" – I’d truly appreciate it!

Thank you!

[hidenseek9]

@Elena_Kalina 

Thank you for the suggestion. 

I fee like we are almost there!

Which gantt chart visual are you referring to?

I am currently using gantt chart by MAQ software and I added "Season ID" to Legend field, but the rows are still separated by season IDs as per below. 

I do not know where "Color Saturation" field is so I could not try that part. 

Thank you for the support!

[Elena_Kalina · Accepted solution]

You're using the Gantt by Microsoft Corporation visual from Power BI Marketplace. You created a calculated column for Launch Date instead of a measure, but need to enable "Group tasks" to show all seasons in a single row per product.

Launch Date Column = 
VAR CurrentProduct = 'Daily Sales'[Product Name]
VAR CurrentSeason = 'Daily Sales'[Season ID]
RETURN
    CALCULATE(
        MIN('Daily Sales'[Date]),
        FILTER(
            ALL('Daily Sales'),
            'Daily Sales'[Product Name] = CurrentProduct &&
            'Daily Sales'[Season ID] = CurrentSeason
        )
    )

[hidenseek9]

@Elena_Kalina 

Thank you for the reply. 

Unfortunately, my visual is returning blank as per below. 

I am following your instruction and using Microsoft Gantt chart

Turned on "Group task"

Legend - Season ID (Calculated column)

Task - Product Name

Start Date - Launch date (Calculated column)

End Date - Discontinued Date (Calculated column)

The difference is your example is that discontinued date calculated column in my case is taking the oldest date, whereas it appears in your case, it does not specify it. 

Do you know why my visual is returning blank?

Calculated column on launch date and discontinued date are working perfectly fine in the data. 

[Elena_Kalina]

I am using Measure for Discontinued Date

[hidenseek9]

I also tried the measure for discontinued date, but it is still returning blank visual. 

The measures for both launch date and discontinued date are working properly as well. 

[Elena_Kalina]

In what order do you add speakers and at what stage the visual will become Blank, I mean that maybe we will understand what exactly the problem is.

[hidenseek9]

For the time being, it is managable to have products shown on different rows by season. 

Thank you much for all the support. @Elena_Kalina 

[v-menakakota]

Hi  ,
Thanks for reaching out to the Microsoft fabric community forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). 

Do not include sensitive information. Do not include anything that is unrelated to the issue or question. 

Please show the expected outcome based on the sample data you provided.

Best Regards, 
Menaka.
Community Support Team 

[v-menakakota]

Hi @hidenseek9 ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution. 
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you. 
Thank you for your patience and look forward to hearing from you. 

[v-menakakota]

Hi @hidenseek9 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

Thank you.