DAX Count rows according to date including existing filters

[C-F-G]

Hello experts,

couldn't find a smilar matching topic so I created this one.

I need your help to correct Dax measures which are counting rows older or younger than a given date.

There is an order table with a due date column. I want to count orders according to that due date in relative to today.

Let's say I want to count open orders in a range of +/- 7 days from today.

I managed to set the correct date range dynamically and to count orders in that range for any given day.

But I also want show whats before and after the given date range and I struggle with it. How can I modify my counting measures to display the count of all orders before the date range begins and after the date range has ended. 

When you look at the image, I my table I want in the date column as first entry "older" which counts 9 orders and another entry "after" which should count 49 orders. Alternatively count this orders and add it to first or last day of the date range. Then the 6th Aug would show "11" and the 27th Sep would show "52".

Anyone has an idea?

My counting measures:

• Counting the orders within the date range: 
  SLA_Monitor Count in Range = CALCULATE(COUNT(orders[Aft_sk]))
• Couting in the past, before the date range begins: 
  SLA_Monitor Count before Range = CALCULATE(COUNT(orders[Aft_sk]),FILTER(orders,orders[t_sla_end]<=[SLA_Monitor first date in range]))
• Couting in the future after the date range ended: 
  SLA_Monitor Count after Range = CALCULATE(COUNT(orders[Aft_sk]),FILTER(orders,or(orders[t_sla_end]>=[SLA_Monitor last date in range],ISBLANK(orders[t_sla_end]))))

I set a filter to "1" on the following measure to dynmaically create the x-axis (first column): 
SLA_Monitor Filter Range = IF(SELECTEDVALUE(orders[t_sla_end])>=TODAY()-7 && SELECTEDVALUE(orders[t_sla_end])<=TODAY()+7,1,BLANK())

Thank you and BR

cfg

[parry2k · Accepted solution]

@C-F-G recommendation would be to add a relationship or use treatas function:

Sales Measure = 
VAR __StartDate = TODAY () - 7
VAR __DatesTable = DATESINPERIOD ( 'Calendar'[Date], __StartDate, 14, DAY )
RETURN
CALCULATE (
   SUM ( Table[Sales] ),
   TREATAS (
      __DatesTable,
      Table[Date Column You Want To Use]
  )
)

[C-F-G]

Thank you, parry2k.

The use of TREATAS looks very promising.

I will work on this approach.👍

[v-fenling-msft]

Hi, @C-F-G 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[C-F-G]

Hi,

thank you for trying to help.

Unfortunately I couldn't resolve it yet.

A change in the data model is needed. But I can't do that myself while working on a live connection to an analysis server. I need to contact the other department in order to place my change request.

Nevertheless, thank you very much for your effort.

cfg

[parry2k]

@C-F-G 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

For your calculation you can simply use the following measure to get -/+ 7 days from today:

Sales Measure = 
VAR __StartDate = TODAY () - 7
RETURN
CALCULATE (
   SUM ( Table[Sales] ),
   DATESINPERIOD ( 'Calendar'[Date], __StartDate, 14, DAY )
)

[C-F-G]

Hi parry2k,

thank you for pointing out the importance of a date table.

There is already a date table defined in the model I'm working with. Unfortunately it is linked to other date fields in the order table. I'm working with a live connection on an analysis server and can't change the data model. I have to contact other department to create an (inactive) relationship between the date table and my desired date field. I tried to work around this but it seems it isn't posssible ...

Nevertheless, thank you very much for your effort.

cfg