Data modelling

[Anonymous]

Hi Experts ,

I am new to power BI.

My requirement is to show:   How many tickets are closed , Recieved and open on week basis week starts from (Fri- Thurs).

Jan 17 2020- Jan23 2020 is the 4 th week and below is the data 

I have the base data as below:

Incident    Creation date       Resolved Date           Category            Status 

1                    01/01/2020        18/01/2020               Finance            Resolved 

2                    20/01/2020                                          Finance            In Progress

3                    19/01/2020           21/01/2020            Finance             Resolved 

4                    10/01/2020        22/01/2020               MM                     Resolved 

5                   20/01/2020                                           MM                    In Progress

6                   19/01/2020                                            MM                     In Progress 

7                   01/01/2020          10/01/2020               MM                      resolved 

Incident 7 is not week 4 , Hence we do not need to consider , that means all months data we have .

Then how to calculate the Open , recieved and closed incident to get the result in below manner 

                          Recieved        Closed     Open   

Category 

Finance                 2                  2                 1 

MM                       2                   1                2 

Kindly suggest .

[v-juanli-msft]

Hi @Anonymous 

Create a date table, this date table doesn't have relationship with your tables.

DATE TABLE =
ADDCOLUMNS (
    CALENDARAUTO (),
    "YEAR", YEAR ( [Date] ),
    "Weekday", WEEKDAY (
        [Date],
        2
    ),
    "weeknum", WEEKNUM (
        [Date] + 3,
        2
    ),
    "year-week", YEAR ( [Date] ) & "-"
        & WEEKNUM (
            [Date] + 3,
            2
        )
)

Then create measures in your table

weekstart =
CALCULATE (
    MIN ( 'DATE TABLE'[Date] ),
    FILTER (
        ALLSELECTED ( 'DATE TABLE' ),
        'DATE TABLE'[year-week]
            = MAX ( 'DATE TABLE'[year-week] )
    )
)

weekend =
CALCULATE (
    MAX ( 'DATE TABLE'[Date] ),
    FILTER (
        ALLSELECTED ( 'DATE TABLE' ),
        'DATE TABLE'[year-week]
            = MAX ( 'DATE TABLE'[year-week] )
    )
)

recieved =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Incident] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Category]
            = MAX ( 'Table'[Category] )
            && 'Table'[Creation date] >= [weekstart]
            && 'Table'[Creation date] <= [weekend]
    )
)


open =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Incident] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Category]
            = MAX ( 'Table'[Category] )
            && 'Table'[Creation date] >= [weekstart]
            && 'Table'[Creation date] <= [weekend]
            && 'Table'[Status] = "In Progress"
    )
)


closed =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Incident] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Category]
            = MAX ( 'Table'[Category] )
            && 'Table'[Resolved Date] >= [weekstart]
            && 'Table'[Resolved Date] <= [weekend]
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

If you can share a 2 column Calendar Table with Date in the first column and Week number in the second column, then i can help.

[amitchandak]

Assuming you have date dimension and that is not joined. In case that joins with your table, cross filter need to be used to remove join

Count=
calculate(count(table[incident]),filter(table,(table[creation_date]>=minx(Date,date[Date]) && table[creation_date]<=minx(Date,date[Date]))
				|| (table[Resolved Date]>=minx(Date,date[Date]) && table[Resolved Date]<=minx(Date,date[Date]))
				)

How to use userelation nad cross filter refer : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In case you do not have date dimension

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[kentyler]

I think you will either have to write a lot of complicated DAX, or unpivot your data to take advantage of power bi's automatic calculations.

Consider this format

Incident    date                 Category            Status 

2                    20/01/2020                     Finance            In Progress

5                   20/01/2020                     MM                    In Progress

6                   19/01/2020                      MM                     In Progress 

7                   01/01/2020                        MM                      resolved 

1                      18/01/2020                     Finance            Resolved 

3                    21/01/2020                     Finance             Resolved 

4                    22/01/2020                     MM                     Resolved 

7                   01/01/2020                       MM                      resolved 

this would make it much easier to calculate, for a given month, how many issues were in process, and how many had been closed. I