DAX Measure that accounts for previous dates data and not future dates data based on current date

[npombo]

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.

 

[WinterMist]

@npombo 

No need to apologize! 

Glad it sounds like the solution from @vapid128 will work.

Regards,

Nathan

[WinterMist]

@npombo 

Hope you are doing well.  Was the info provided helpful to you?  Were you able to resolve the issue?

Regards,

Nathan

[npombo]

Hi Nathan,

Apologies for the late response. I attempted the workaround proposed by user vapid128 first, and it appears to have worked. I will be able to know for certain after tracking the potential change in the metric tomorrow.

I appreciate your help/follow up and will update the forum post accordingly once I can confirm or deny this potential solution.

[vapid128 · Accepted solution]

we can average all weeks which have number.

calculate(average([COGS per day]),[COGS per day]>0)

we also can average all numbers before today.

calculate(average([COGS per day]),[week]<today())

[npombo]

Hi Vapid,

The second proposed solution did in fact work for my needs. Thank you for your help! 

[WinterMist]

@npombo 

Interesting that you mention this.

I was seeing the same behavior on a separate problem using TOTALYTD earlier today.  TOTALYTD should stop at today's date, but it was giving me future dates, just like you said.

As a workaround, using the simple DATE & TODAY functions resolved the problem for me.

Try something like this.

Hope this is helpful to you.

Regards,

Nathan