Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
npombo
Helper I
Helper I

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: 

npombo_3-1658447993393.png

 

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.

 

1 ACCEPTED SOLUTION
vapid128
Solution Specialist
Solution 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())

View solution in original post

6 REPLIES 6
WinterMist
Impactful Individual
Impactful Individual

@npombo 

 

No need to apologize! 

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

 

Regards,

Nathan

WinterMist
Impactful Individual
Impactful Individual

@npombo 

 

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

 

Regards,

Nathan

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

Hi Vapid,

 

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

WinterMist
Impactful Individual
Impactful Individual

@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.

 

WinterMist_0-1658452710094.png

Hope this is helpful to you.

 

Regards,

Nathan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors