Solved: Measure with Date table

Pacifo · ‎06-03-2020

Hi all,

I am building a dashboard for our call reporting system to assist with monthly reporting. I have the following setup:

Date table which has no relationships
Table called "Requests" with fields
- RequestNumber|CreatedOn|SolvedOn

As you can imagine, RequestNumber is a call number (e.g. 323456), CreatedOn is a start date, and SolvedOn is a end date when the request gets closed (this can also be null). Now I have created a Measure called "Open Requests" which checks if a call was open during the date. Code below.

Open Requests = 
VAR MinDate= MIN( MasterDate[Date] )
VAR MaxDate= MAX( MasterDate[Date] )
RETURN
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( list_requests[requestNumber] ),
        list_requests[createdOn] <= MaxDate,
        list_requests[solvedOn] >= MinDate
),ALL( MasterDate ))

This code seemingly works great, so I tried to replicate this for Solved Requests with the following code.

Solved Requests = 
VAR MinDate= MIN( MasterDate[Date] )
VAR MaxDate= MAX( MasterDate[Date] )
RETURN
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( list_requests[requestNumber] ),
        list_requests[solvedOn] <= MaxDate,
        list_requests[solvedOn] >= MinDate
),ALL( MasterDate ))

In my dashboard, I am using the Date fields from the Date table as a slicer. In my slicer I have selected week 13 in 2020, which returns the 7 dates. As you can see in the table below Open Requests gives me a breakdown by day, whereas Solved Requests gives me a total (the total is correct). I would like Solved Requests to look like Open Requests totaling the figures each day (as I want to plot the lines on a line chart).

I think I am missing something simple here? I tried counting just solved requests, but this seemingly ignores my Date slicer and totals every solved request in my table.

Is somebody able to point me in the right direction?