topic Re: DAX Measure that accounts for previous dates data and not future dates data based on current date in DAX Commands and Tips

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

npombo — Fri, 22 Jul 2022 00:16:56 GMT

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.

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

WinterMist — Fri, 22 Jul 2022 01:20:06 GMT

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

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

vapid128 — Fri, 22 Jul 2022 01:21:12 GMT

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())

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

WinterMist — Mon, 25 Jul 2022 21:13:15 GMT

npombo

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

Regards,

Nathan

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

npombo — Mon, 25 Jul 2022 22:28:30 GMT

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.

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

WinterMist — Mon, 25 Jul 2022 22:56:47 GMT

npombo

No need to apologize!

Glad it sounds like the solution from vapid128 will work.

Regards,

Nathan

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

npombo — Wed, 27 Jul 2022 15:08:58 GMT

Hi Vapid,

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