cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

DAX Help

Needs to calculate Last 12 months average inventory

 

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 backward and check inventory value on 04.06.20, 04.05.20…………….. 04.07.19 (12 months)

Add all twelve months inventory and divide it by 12 so that you will get average inventory.

 

Average daily net sales =  say selected date 04.06.20 then ( Net sales from 04.07.19 to 04.06.20 (12 months) divide it by 12)              

 

Guys please help on this

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: DAX Help

Hi,

 

Did you have a calendar table with relationship with your fact table?

 

You can use the following dax to calculted rolling average inventory for last 12 months:

 

measure =
VAR _DATE =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Inventory] ),
        DATESBETWEEN ( 'Date'[Date], DATEADD ( _DATE, -12, MONTH ), _DATE )
    ) / 12

 

It will be the same for Net Sales. Or you can use quick measure in power bi to calculate rolling average. Please refer to https://www.mssqltips.com/sqlservertip/5635/creating-a-rolling-average-graph-with-power-bi/.

 

It will be nice if you can show us some sample data by onedrive for business.

 

Best Regards,

Dedmon Dai

View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: DAX Help

@SonaSingh123 , with a date calendar

 


Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

 

Or First Sum at month level and then take Avg

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

Avg Rolling 12 = AverageX(summarize(Sales,Date[Month Year],"_1",[Rolling 12]),[_1])

Or [Rolling 12]/12

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Patron
Post Patron

Re: DAX Help

In my case, the user will select one date, and I have to average inventory based on that date.

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 backward and check inventory value on 04.06.20, 04.05.20…………….. 04.07.19 (12 months)

Add all twelve months inventory and divide it by 12 so that you will get average inventory.

 

Average daily net sales =  say selected date 04.06.20 then ( Net sales from 04.07.19 to 04.06.20 (12 months) divide it by 12)              

Highlighted
Microsoft
Microsoft

Re: DAX Help

Hi,

 

Did you have a calendar table with relationship with your fact table?

 

You can use the following dax to calculted rolling average inventory for last 12 months:

 

measure =
VAR _DATE =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Inventory] ),
        DATESBETWEEN ( 'Date'[Date], DATEADD ( _DATE, -12, MONTH ), _DATE )
    ) / 12

 

It will be the same for Net Sales. Or you can use quick measure in power bi to calculate rolling average. Please refer to https://www.mssqltips.com/sqlservertip/5635/creating-a-rolling-average-graph-with-power-bi/.

 

It will be nice if you can show us some sample data by onedrive for business.

 

Best Regards,

Dedmon Dai

View solution in original post

Highlighted
Post Patron
Post Patron

Re: DAX Help

But my client is asking average inventory calculation like below

 

From the selected date by user say 04.06.2020,

go backward and check inventory value on 04.06.20, 04.05.20…………….. 04.07.19 (12 months)

Add all twelve months inventory and divide it by 12 so that you will get average inventory.

Highlighted
Microsoft
Microsoft

Re: DAX Help

Hi @SonaSingh123 ,

 

Please see my edited reply.

 

Best Regards,

Dedmon Dai

 

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors