Days of supply question

[Renu15]

I want to understand how do i calculate the days of supply as shown below in powerBI based on product and site. Can anyone suggest how i do it in powerbi as I have done in excel.

DSI of jan 2021= (SUM OF DAYS IN NEXT 3 MONTHS* Jan End Inv)/(sum of demand for next 3 months)

Product Family	Sites	Quarter	No.of Month	Month	2021 On Hand Inv	2021 Demand	Month-Year	No of days	sum of next month   demand	sum of days in next 3 months	DSI
ABC	XYZ	Q1	1	Jan	73490.84067	38851.33	Jan-2021	31	124375	89	52.5885616
ABC	XYZ	Q1	2	Feb	71827.50733	38863.33	Feb-2021	28			`
ABC	XYZ	Q1	3	Mar	71094.174	41933.33	Mar-2021	31
ABC	XYZ	Q2	4	Apr	67316.174	43578	Apr-2021	30
ABC	XYZ	Q2	5	May	62344.174	44572	May-2021	31
ABC	XYZ	Q2	6	Jun	56666.174	45478	Jun-2021	30
ABC	XYZ	Q3	7	Jul	52703.025	43563.15	Jul-2021	31
ABC	XYZ	Q3	8	Aug	50411.876	43491.15	Aug-2021	31
ABC	XYZ	Q3	9	Sep	40450.727	41961.15	Sep-2021	30
ABC	XYZ	Q4	10	Oct	41373.35007	38077.38	Oct-2021	31
ABC	XYZ	Q4	11	Nov	45791.97314	35381.38	Nov-2021	30
ABC	XYZ	Q4	12	Dec	53916.59621	31475.38	Dec-2021	31

[parry2k]

@Renu15 How about 4:00 CST? Send me an email (my email is in my signature)

[Renu15]

I have sent you the zoom link through my id. Please just check it in case you recieved or not !

[parry2k]

@Renu15 Hey sorry what is not working, are you ok to share your pbix file or we can do a team/zoom meeting and I can have a quick look.

[Renu15]

Yes, we can have a zoom call. I am in USA time zone. I am unable to load the pbix file. At what time you are available ? I am available in between 3.30-4.00 P.m CST.

Please let me know and I can send the zoom link. It will be good to discuss the issue verbally as I need it very urgentl for my executives! Thanks alot !

[Renu15]

@amitchandakcan you please review this problem and suggest what I should. I used code [rovided by Parry but it is not working for me !

[parry2k]

@Renu15 seems like you don't have a date column in your model. change this code as below

Calendar=
VAR __dates = CALENDAR ( "2021,01,01", "2021,12,31" )
RETURN
ADDCOLUMNS (
    __dates,
    "Year",             YEAR ( [Date] ),
    "Month Number",     MONTH ( [Date] ),
    "Month Name",       FORMAT ( [Date], "MMMM" ), --use MMMM for full month name, January instead of Jan
    "Month",            FORMAT( [Date], "MMM, YYYY" ), --use MMMMM for full month name, January instead of Ja
    "Month Sort",       FORMAT( [Date], "YYYY-MM" ),
    "Quarter",          "Q" & FORMAT( [Date], "Q, YYYY" ),
    "Quarter Sort",     FORMAT ( [Date], "YYYY-Q" )
)

and make sure in your data table you have a date column that you can set the relationship on, between these two tables.

[parry2k · Accepted solution]

@Renu15 hey you should add a date dimension in your model, you can add one from my blog post here, once it is done, add the following measure for DSI

DSI = 
VAR __DateTable3Months = DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( MAX ( 'Calendar'[Date] ), 0 ) + 1, 3, MONTH ) 
VAR __DemandsNext3Months = 
CALCULATE ( 
    SUM ( [2021 Demand] ), 
    __DateTable3Months
)
VAR __DaysNext3Month = 
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        __DateTable3Months
    )
RETURN
    DIVIDE ( __DemandsNext3Months, __DaysNext3Month )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Renu15]

@parry2kcan you please help me? I even added the date dimension and logic which you have given is not working.

[Renu15]

I added a date dimesnion in my data and your code is not working.

[Renu15]