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 am having some trouble with getting a measure to behave the way that I want it to. I have attached a screenshot for context. Basically it is a production plan of when my company are bringing stock in of a certain item (which has been filtered using the standard filter tab at the side of the report). The measure I am having trouble to create is STOCK BUILD.
Stock Build is equal to the stock in the building, plus what has already been sold so far in the year, plus stock that has been ordered but hasn't yet arrived.
Stock Build = sum(Stocks[Stock.InStock])+ IF( ISFILTERED('Dates'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), TOTALYTD(SUM('ETAs'[Quantity]), 'Dates'[Date].[Date]) )+8766
Sales Measure = TOTALYTD(sum(Sales[Sale.Volume]),Dates[Date])
The last term in 'Stock Build' is 8766. The reason is that this is the volume of product 'Gooey Louie' we have sold YTD. So what I need in its place is a YTD sales volume measure, that can be filtered by product, but is not filtered by date e.g. when in a table with months as rows... I want it to be 8766 (or whatever the YTD sales is for the product) regardless of what date filters are implicit in choice of visual.
I do hope that I have managed to articulate my question. I've found this to be very tricky.
Solved! Go to Solution.
According to your description, you want to have your Sales Measure to ignore the month filter so that it will always show Year Total Sales instead of YTD Sales. Right?
If so, you just create a Year column in your date table.
Year = YEAR(Dates[Date])
Then use ALLEXCEPT() to group the calculation on Year.
Sales Measure = CALCULATE(SUM(Sales[Sale.Volume]),ALLEXCEPT(Dates,Dates[Year]))
Regards,
According to your description, you want to have your Sales Measure to ignore the month filter so that it will always show Year Total Sales instead of YTD Sales. Right?
If so, you just create a Year column in your date table.
Year = YEAR(Dates[Date])
Then use ALLEXCEPT() to group the calculation on Year.
Sales Measure = CALCULATE(SUM(Sales[Sale.Volume]),ALLEXCEPT(Dates,Dates[Year]))
Regards,
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |