Calculate a Measure Based on Date Filter

cmac1 — Thu, 10 Dec 2020 05:02:52 GMT

I am trying to calculate a measure based on my date filter in the report. Here is the formula I am using to count the days between between the start and end date of the filter:

Count Days =

var start_Date=FIRSTDATE(ALLSELECTED('calendar_auto'[Date]))

var end_Date=LASTDATE(ALLSELECTED('calendar_auto'[Date]))

var current_Start=MIN('public inventory_binadjustment'[transaction_date_local])

var current_end=MAX('public inventory_binadjustment'[transaction_date_local])

return

DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY)

It returns the correct amount of days. My next step is to have another measure that calculates the average daily usage of each item in the table based on that amount of days. In the example the days from the filter represents 182 days. However, you can see in the table the "Count Days" is looking at the first and last dates for that specific item instead of the complete 182 days.

Any suggestion on how I can get the measure to "Average Daily Usage" to calculate based on the first and last dates of the filter instead of just the days for that specific item?

Re: Calculate a Measure Based on Date Filter

lbendlin — Sun, 27 Dec 2020 03:21:35 GMT

whenever you see a behavior like that you need to think about the filter context. By default it only looks at the current "row". You need to use ALL() or ALLSELECTED() on your fact table (not the dates table) to expand the context according to your requirements.

Please provide sample data in usable format and show the expected outcome.

topic Calculate a Measure Based on Date Filter in DAX Commands and Tips

Calculate a Measure Based on Date Filter

Re: Calculate a Measure Based on Date Filter