Inventory data with seasonal products

[MrMP]

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

Thank you for your help!

[Anonymous]

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.

[MrMP]

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

[Anonymous]

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.

[MrMP]

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!

[Anonymous]

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.

If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

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.

[MrMP]

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!

[Anonymous]

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.

[MrMP]

How about this OneDrive link?

[VahidDM]

Hi @MrMP 

Download the attached file to find the measures that you need to add to your report:

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[MrMP]

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)