cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Compute stat for date range and same day of week

I have a user that wants a table that computes the number of invoices late for a date range and then provides a summary for the whole period. I'm having trouble even conceptualizing how the calculate or filter DAX would be. I tried joining to a duplicate table on the period alone, but when I put it in a visualization it shows the same total for everything/for the entire dataset.

Data:

Desired Output

1 ACCEPTED SOLUTION
Community Support

Hi @jondufault ,

According to your description, here's my solution.

1.Create a new Status table as below, don't make relationship with the fact table.

2.Create two measures:

``````Count Date =
COUNTROWS (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[status] = MAX ( 'Status'[Status] ) )
) + 0
``````
``````Count Period =
COUNTROWS (
FILTER (
ALL ( 'Table' ),
'Table'[period]
IN VALUES ( 'Table'[period] )
&& 'Table'[status] = MAX ( 'Status'[Status] )
)
)
``````

Put the new status column and two measures in a visual, get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi @jondufault ,

According to your description, here's my solution.

1.Create a new Status table as below, don't make relationship with the fact table.

2.Create two measures:

``````Count Date =
COUNTROWS (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[status] = MAX ( 'Status'[Status] ) )
) + 0
``````
``````Count Period =
COUNTROWS (
FILTER (
ALL ( 'Table' ),
'Table'[period]
IN VALUES ( 'Table'[period] )
&& 'Table'[status] = MAX ( 'Status'[Status] )
)
)
``````

Put the new status column and two measures in a visual, get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.