Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Help

Please help guys Days of Inventory in Hand (DIH) =  Average Inventory / Average daily Net Sales.   Now how to arrive average inventory:   From the selected date by user say 04.06.2020, go backwa...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    You will need a calendar table.

    CALENDAR = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))

Measure =
CALCULATE (
    AVERAGE ( 'Table'[inventory] ),
    FILTER (
        'Table',
        'Table'[date] <= SELECTEDVALUE ( 'CALENDAR'[Date] )
            && 'Table'[date] >= EDATE ( SELECTEDVALUE ( 'CALENDAR'[Date] ), -12 )
    )
)
    / CALCULATE (
        AVERAGE ( 'Table'[net sales] ),
        FILTER (
            'Table',
            'Table'[date] <= SELECTEDVALUE ( 'CALENDAR'[Date] )
                && 'Table'[date] >= EDATE ( SELECTEDVALUE ( 'CALENDAR'[Date] ), -12 )
        )
    )

     

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

You will need a calendar table.

CALENDAR = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))

Measure =
CALCULATE (
    AVERAGE ( 'Table'[inventory] ),
    FILTER (
        'Table',
        'Table'[date] <= SELECTEDVALUE ( 'CALENDAR'[Date] )
            && 'Table'[date] >= EDATE ( SELECTEDVALUE ( 'CALENDAR'[Date] ), -12 )
    )
)
    / CALCULATE (
        AVERAGE ( 'Table'[net sales] ),
        FILTER (
            'Table',
            'Table'[date] <= SELECTEDVALUE ( 'CALENDAR'[Date] )
                && 'Table'[date] >= EDATE ( SELECTEDVALUE ( 'CALENDAR'[Date] ), -12 )
        )
    )

 

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

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