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.

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , With date table you should be able to get rolling like 

Rolling 12 = CALCULATE(sum(Sales[Net Sales]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-12,MONTH)) 

Or
last 12 month = 
var _max = today() 
// or  // maxx(allselected('Date'),[date]) 
var _min = date(year(_max)-1,month(_max),Day(_max))
return
CALCULATE(Sum('Table'[Column]),filter(all('Date'),'Date'[Date]<=_max && 'Date'[Date]>=_min))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

But  here my requirement is different