Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MrMP
Helper III
Helper III

Inventory data with seasonal products

 
Hi all,

 

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

I have two tables:

 

DateProductLocationInventory AmountSeason (info comes from 2nd table based on Product and Date)
01-01-2022Product1Geo1500Season 1
05-01-2022Product1Geo1450Season 1
15-01-2022Product1Geo1480Season 2
25-01-2022Product1Geo1350Season 3

 

Date FromDate ToProductSeason
01-01-202214-01-2022    Product1Season 1
15-01-2022    24-01-2022    Product1Season 2
25-01-2022    31-01-2022    Product1Season 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:

 

ProductGeoInventory AmountSeason
Product1Geo1480Season 2

 

Thank you for your help!

10 REPLIES 10
v-rongtiep-msft
Community Support
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.

vpollymsft_0-1651651311672.png

vpollymsft_1-1651651325204.png

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.

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

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")

vpollymsft_0-1651716894395.png

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.

 

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!

v-rongtiep-msft
Community Support
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.

vpollymsft_0-1649826628574.png

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.

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!

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.

 

How about this OneDrive link?

Hi @MrMP 

 

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

 

Output:

VahidDM_0-1649638044800.png




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/

 

 

 

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)
    
    

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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