cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhorvati Frequent Visitor
Frequent Visitor

Divide By Number of Months YTD

I want to divide by total inventory by the number of months YTD.   The count function gives me all of the months in my calendar table.  How do I filter it just for the current year that will be a filtered selection?    Example:  So if my data is through May, it will divide by 5. 

 

 

Averge Inventory = DIVIDE(CALCULATE([Inventory], DATESYTD(d_DateTable[Date])), COUNT(d_DateTable[Month]))

 

 

I researched forums and tried a dozen different fomulas.  Sorry, I am beginner.  

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Divide By Number of Months YTD

Hi @bhorvati,

 

I'm assuming your calendar table is related with your inventory table.

 

Create a month column on your inventory table then change your measure to:

 

Average Inventory_distinct_Count =
DIVIDE (
    CALCULATE ( [Inventory]; DATESYTD ( d_DateTable[Date] ) );
    MAX('Table'[Month_])
)

When you divide the number by count you will get every single row so in January you will get 31 days, you need to get the distinct number of that month in this case I have it by the MAX formula of the inventory table:

 

total.png

 

 

Regards,

MFelix

 

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7

Re: Divide By Number of Months YTD

Try this

 

Months YTD =  totalytd(countrows(values(d_DateTable[Month])),d_DateTable[Date])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: Divide By Number of Months YTD

Hi @bhorvati,

Instead of COUNT use DISTINCTCOUNT should work as you need.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bhorvati Frequent Visitor
Frequent Visitor

Re: Divide By Number of Months YTD

Hi @MattAllington Solution provided, returned a month count of 12 when my data is through May, thus i need the count to be 5.   The only page filter I have on is year.  

 

By the way, I've been watching your videos on YouTube.  Thanks sharing all the content and advice.  

Super User
Super User

Re: Divide By Number of Months YTD

Hi @bhorvati,

 

I'm assuming your calendar table is related with your inventory table.

 

Create a month column on your inventory table then change your measure to:

 

Average Inventory_distinct_Count =
DIVIDE (
    CALCULATE ( [Inventory]; DATESYTD ( d_DateTable[Date] ) );
    MAX('Table'[Month_])
)

When you divide the number by count you will get every single row so in January you will get 31 days, you need to get the distinct number of that month in this case I have it by the MAX formula of the inventory table:

 

total.png

 

 

Regards,

MFelix

 

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

bhorvati Frequent Visitor
Frequent Visitor

Re: Divide By Number of Months YTD

@MFelix  Thank your for your reply, but can you look at my formula and see if you can see what I am doing wrong.   My formula is still picking up the MAX month in the date table which is 12.

 

Capture.PNG

Super User
Super User

Re: Divide By Number of Months YTD

Hi @bhorvati,

The division must be made by the MAXIMUM month in you inventory table not the dste table.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
bhorvati Frequent Visitor
Frequent Visitor

Re: Divide By Number of Months YTD

@MFelix   I finally got it.   I have been having the same issue with a barrels per day formula.  I've been having to filter every report page for month to get my reports to work correctly.  This has been a HUGE help.  Thanks for taking the time.  Much appreciated. 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,580)