Forum Discussion
Helper IDAX 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.
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())
6 Replies
- WinterMist
Impactful Individual
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
vapid128Solution Specialist
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())
npomboHi Vapid,
The second proposed solution did in fact work for my needs. Thank you for your help!
- WinterMist
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.
- WinterMist
