Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTry this
Months YTD = totalytd(countrows(values(d_DateTable[Month])),d_DateTable[Date])
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.
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:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |