Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Joshi_M
Helper I
Helper I

Future -Days of Supply

Hi,

I have gone through the below Link.

https://community.powerbi.com/t5/Desktop/Days-of-supply/td-p/635223

 

I am not able to apply it to the below data.

See below Table. (Data is coming from Matrix Visual in Power BI)

  1. Main Data is coming from a direct query.
  2. Slicers selected for Location and Material Group.
  3. Fields available in direct query - Material ,Location, Material Group,Demand and Receipts and Closing stock,Index weeks and Week/Year which is in text format.
  4. Index weeks vailable in the direct query. Current week has index 0. can go beyond  52 weeks
  5. All calculation need to move considering Index 0 and ahead.
  6. Created a Measure for Future closing stock since future closing stock not correct in direct query
  7. Already created maesures  Total Closing stock ,Total Opening Stock Cumulative Demand & Cumulative Receipts.
  8. Example of Closing stock measure created,Total Closing Stock = SUMX(FILTER(ALLEXCEPT(Table1,Table1[Plant Text],Table1[Material Group Family],Table1[Material]),Table1[Index weeks]>=0 && Table1[Index weeks]<=MAX(Table1[Index weeks])),[Act Stock])
  9. Need a measure to create a measure for calculating supply days  based on the above conditions
  10. Example Index week 0 - Closing stock -239 = Days of supply = 6*7 + (4/14 )*7 shown only 1st week.

Hope the above is clear.Showing Finally below the table is the Matrix visual on how i need to present It.

Been struggling for a long time.kindly help

any additional inputs requied let me know.

 

Regards

Joshi_M

 

MaterialLocationMaterial GroupTotal Opening StockTotal DemandCumulative Openig DemandTotal ReceiptsCumulative Opening ReceiptsTotal Closing StockWeek/YearIndex weeks
R5AB 10 002392022.180
R5AB239606040402192022.191
R5AB2196012039791982022.22
R5AB198601800791382022.213
R5AB13827207391181502022.224
R5AB15014221391571752022.235
R5AB1751423501571612022.246
R5AB1611424901571472022.257
R5AB1471526401571322022.268
R5AB1321628001571162022.279
R5AB1161629601571012022.2810
R5AB101163120157852022.2911
R5AB85163280157702022.312
R5AB70533319176842022.3113
R5AB8453380176802022.3214

 

Index weeks123456789101112131415
Row Labels2022.182022.192022.22022.212022.222022.232022.242022.252022.262022.272022.282022.292022.32022.312022.32
Sum of Total Opening Stock 239219198138150175161147132116101857084
Sum of Total Demand1060606027141414151616161655
Sum of Cumulative Openig Demand 60120180207221235249264280296312328333338
Sum of Total Receipts04039039390000000190
Sum of Total Closing Stock23921919813815017516114713211610185708480
Stock Cover Days44              

 

 

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Joshi_M,

You can try to use the following measure formula if helps:

supply days =
VAR currWeek =
    MAX ( Table1[Index weeks] )
VAR closeStock =
    CALCULATE (
        SUM ( Table1[Act Stock] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Index weeks] >= 0
                && Table1[Index weeks] <= currWeek
        ),
        VALUES ( Table1[Plant Text] ),
        VALUES ( Table1[Material Group Family] ),
        VALUES ( Table1[Material] )
    )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [Plant Text],
        [Material Group Family],
        [Material],
        [Index weeks],
        "Demand",
            CALCULATE (
                SUM ( Table1[Total Demand] ),
                FILTER (
                    ALLSELECTED ( Table1 ),
                    Table1[Index weeks] >= currWeek 
                        && Table1[Index weeks] <= EARLIER ( Table1[Index weeks] )
                ),
                VALUES ( Table1[Plant Text] ),
                VALUES ( Table1[Material Group Family] ),
                VALUES ( Table1[Material] )
            )
    )
VAR _lastweek =
    MAXX ( FILTER ( summary, closeStock - [Demand] >= 0 ), [Index Week] )
RETURN
    (_lastweek - currWeek)*7

Regards

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Sheng ,

Many thanks for Your  response.

Need a little more of  your help with the below part.

The [Act Stock] for me is a measure which goes like below 

IF( Sum(Table1[Index weeks])=0,Sum(Table1[Stock on Hand ending in To]),( Sum(Table1[Purchase Order in To])+Sum(Table1[Goods in Transit in To])-Sum(Tabele1[Demand in To])))
How to use this in below since  CALCULATE (Sum( Table1[Act Stock]) indicates an error.
Note : i already have a measure for closestock as earlier mentioned .SUMX(FILTER(ALLEXCEPT(Table1,Table1[Plant Text],Table1[Material Group Family],Table1[Material]),Table1[Index weeks]>=0 && Table1[Index weeks]<=MAX(Table1[Index weeks])),[Act Stock]). Can this be used somehow in your solution which you gave...
Note : it is dynamic table and keeps changing.
VAR closeStock =
    CALCULATE (
        SUM ( Table1[Act Stock] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Index weeks] >= 0
                && Table1[Index weeks] <= currWeek
        ),
        VALUES ( Table1[Plant Text] ),
        VALUES ( Table1[Material Group Family] ),
        VALUES ( Table1[Material] )
    )

Highly appreciate your help on this.Thanks again

Hoping to hear from you.

Regards

Joshi_M

Hi Sheng ,

I have some progress. I see some values for supply days.

I changed the DAX for closeStock as below.

Rest all I kept as is.

VAR closeStock=SUMX(
FILTER(ALLEXCEPT(Table1,Table1[Plant Text],Table1[Material Group Family],Table1[Material]),Table1[Index weeks]>=0 && ZBE_12WEEK_DEMAND_AP[Index weeks]<=currWeek),
[Act Stock])

I got the output (values) for  supply days but it is not correct.

Showing the same static values for all Material and all weeks.

Please help to identify what may have gone wrong ans what  needs to be changed.

Apppreciate your help

 

Regareds 

Joshi _M

 

HI @Joshi_M,

I think this should be related to allexcept function, it will ignore the filter effects that are not declared in the function:

VAR closeStock = SUMX (
    FILTER (
        ALLSELECTED ( Table1 ),
        [Plant Text] = EARLIER ( Table1[Plant Text] )
            && [Material Group Family] = EARLIER ( Table1[Material Group Family] )
            && [Material] = EARLIER ( Table1[Material] )
            && Table1[Index weeks] >= 0
            && ZBE_12WEEK_DEMAND_AP[Index weeks] <= currWeek
    ),
    [Act Stock]
)

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors