Manage multiple dates in same table

[fabiomanniti]

Hello, I'm having a little issue with this table which has multiple date fields.

The table it's Issues.

And the fields are

• Created_date
• Updated_date
• Resolution_date
• Issue_id
• Issue_status (e.g.: "In progress", "Done", "Cancelled", "Waiting for confirmation"...)

Now, I would like to put a date filter on a page which is "Last week" and in that period of time I would like to know how many ticket are solved, how many are created and so on...

Same thing if I want to create a table:

if I put

• Created_date
• Count(Issue_id)

I get the number of Issues created in that date.

If I put

• Resolution_date
• Count(Issue_id)

I get the number of Issues solved in that day; and so on...

The thing is I would like to put one single date column and multiple fields to know how many Issues have been solved, or created or updated in that day.

If I create a Date dimension table I can link with only one field of my Issues table, so in my page there will always be a "date leader".

Does anyone have the same problem?

FYI: I cannot handle the source of data because it doesn't depend on me. I can only work on Power BI (DAX and Power Query)

[v-chenwuz-msft · Accepted solution]

Hi @fabiomanniti ,

How many tickets solved in last week?

Create a measure via the above expression and let it show items is 1 in filter pane on this visual however can not be on this page.

last week =
VAR _laststart =
    TODAY () - WEEKDAY ( TODAY (), 2 ) - 7
VAR _lastend = _laststart + 6
RETURN
    IF (
        SELECTEDVALUE ( 'Issues'[Resolution_date] ) >= _laststart
            && SELECTEDVALUE ( 'Issues'[Resolution_date] ) <= _lastend,
        1
    )

The use sum() to calculate as you want. if you really want to put it on this page, you can add a column to the issues table, then add to the filter pane on this page.

Please share some example data if you need more help.

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak]

@fabiomanniti , Join Date of date table with all dates. Other than one all will be inactive.  Then use userealtionship in measure to activate the same

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

or

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...