cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## Inventory data with seasonal products

Hi all,

(Reposting in case someone missed it. Help is quite urgent)

I have two tables:

 Date Product Location Inventory Amount Season (info comes from 2nd table based on Product and Date) 01-01-2022 Product1 Geo1 500 Season 1 05-01-2022 Product1 Geo1 450 Season 1 15-01-2022 Product1 Geo1 480 Season 2 25-01-2022 Product1 Geo1 350 Season 3

 Date From Date To Product Season 01-01-2022 14-01-2022 Product1 Season 1 15-01-2022 24-01-2022 Product1 Season 2 25-01-2022 31-01-2022 Product1 Season 3

To summarize, the first table is inventory stock for a product on a day and 2nd table is a mapping what was the season for that product. So when user selects, for example, date range 01-01-2022 to 15-01-2022, for that range product was in two seasons (season 1 and 2), but report should return only the season for the end date of that selection. The end table should be:

 Product Geo Inventory Amount Season Product1 Geo1 480 Season 2

10 REPLIES 10
Community Support

Hi @MrMP ,

Please refer to my pbix file to see if it helps you.

Create a column in Calendar table.

``````Column =
IF (
'Calendar'[Date] >= DATE ( 2021, 2, 1 )
&& 'Calendar'[Date] <= DATE ( 2021, 7, 31 ),
"Spring Summer Season",
"Other Season"
)
``````

Then put the column in the matrix.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

Hi. Thank you for looking into this! Every product has its own seasonality, so it cant stay in calendar table.

Community Support

Hi @MrMP ,

Maybe you can use measure and try to use table visual.

``Measure = IF(MAX('Calendar'[Date])>=DATE(2021,2,1)&&MAX('Calendar'[Date])<=date(2021,7,31),"Spring Summer Season","Other Season")``

Or maybe you can create another calendar table and create relationships between the two calendar tables.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

Hi,

Can you please explain, what do you mean by "another calendar table"?

Also, I cant use this measure. Every product has its own seasonality in different time of the year.

Thank you!

Community Support

Hi  @MrMP ,

Please refer to my pbix file to see if it helps you.

Create a date table first.

``date table = CALENDAR(DATE(2022,1,1),DATE(2022,10,1))``

Then create a measure.

``````Measure2 =
VAR slicer_ =
CALCULATE ( MAX ( 'date table'[Date] ), ALLSELECTED ( 'date table' ) )
VAR maxdate_ =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] <= slicer_ )
)
RETURN
IF ( MAX ( 'Table'[Date] ) = maxdate_, 1, BLANK () )
``````

Then put the data table into the slicer, and filter the data.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

Hi,

Ive managed to create a sample file, (I cannot attach pbix, so I used zippyshare) and the comments of the expected results are in pbix. zippyshare link

Thank you!

Community Support

Hi @MrMP ,

I can't access the data due to privacy regulations, can you upload it in a different way?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper III

MVP

Hi @MrMP

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper III

Thank you very much Vahid!!!

I think im close to making it work.

Is it possible to have the output in a table where Season, Location, Product are dimensions not measures.

 Season (Dimension coming from table 2) Product (Dimension coming from table 2) Location (Coming from table 2) Inventory Amount Measure - (populated based on time selection and season when product was active)

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors