DAX Measure that accounts for previous dates data and not future dates data based on current date
Hello everyone, I am working on a financial report that is connected to an excel document full of referenced data. In Power BI desktop, I am hoping to display the average COGS per day (Cost of Goods Sold) in the form of a PBIX card on a Year to Date scale. Chronologically speaking, the data is organized by week (Mon-Sun). I have references set up in excel to capture future data as it occurs for this year. Below is an example mockup to better visualize my data/request: The issue I am having here is that the average COGS/day is taking into account future weeks where the data is evidently zero. For instance, today's date is 7/21, so all future weeks (weeks of 7/25, 8/1, etc.) are being included in this average and are therefore skewing this metric. I am hoping to write a measure that only accounts for weeks prior to the current date. Any help/tips on my ask would be very appreciated! Thank you in advance.
TOP N with condition on another measure
Hello Guys, I need your help, because I a have a fact table like this : I need to identify the product with the best average "Note", but only with products sold over to 50 units (sum of quantity). With the summarize/addcolumns, it's easy to retrieve for each product the both information : "average note" & "total quantity": As, you can see, the TOP1 Product, with the best average note AND with more of 50 units sold is Product C. Please, can you help me to find the correct formula to apply the top 1 on the "Summarize table" but with the filter on the "Total Quantity" (or another method...) ? For information, I have a fact table with a lot of dimension (date, product, region, Supplier etc...) and the formula need to work with all slicer in the report. Thanks in advance.
