Data modelling

Question

Hi Experts ,I am new to power BI.My requirement is to show:&nbsp; &nbsp;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&nbsp;I have the base data as below:Incident&nbsp; &nbsp; Creation date&nbsp; &nbsp; &nbsp; &nbsp;Resolved Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01/01/2020&nbsp; &nbsp; &nbsp; &nbsp; 18/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Resolved&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; In Progress3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 19/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Resolved&nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/01/2020&nbsp; &nbsp; &nbsp; &nbsp; 22/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Resolved&nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; In Progress6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;19/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;In Progress&nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolved&nbsp;&nbsp;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&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Recieved&nbsp; &nbsp; &nbsp; &nbsp; Closed&nbsp; &nbsp; &nbsp;Open&nbsp; &nbsp;Category&nbsp;Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;&nbsp;Kindly suggest .

amitchandak · Answer

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 joinCount=
calculate(count(table[incident]),filter(table,(table[creation_date]&gt;=minx(Date,date[Date]) &amp;&amp; table[creation_date]&lt;=minx(Date,date[Date]))
				|| (table[Resolved Date]&gt;=minx(Date,date[Date]) &amp;&amp; table[Resolved Date]&lt;=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-trend/ba-p/882970&nbsp;In case you do not have date dimensionTo 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.Referhttps://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions https://www.archerpoint.com/blog/Posts/creating-date-table-power-bihttps://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/&nbsp;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-trendPower-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-RangesConnect on Linkedin

kentyler · Answer

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 formatIncident&nbsp; &nbsp; date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status&nbsp;&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; In Progress5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; In Progress6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;19/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;In Progress&nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolved&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18/01/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Resolved&nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21/01/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Finance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Resolved&nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22/01/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Resolved&nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolved&nbsp;&nbsp;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

ashish_mathur · Answer

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.

v-juanli-msft · Answer

Hi&nbsp;Anonymous&nbsp;
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] ) &amp; "-"
        &amp; 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] )
            &amp;&amp; 'Table'[Creation date] &gt;= [weekstart]
            &amp;&amp; 'Table'[Creation date] &lt;= [weekend]
    )
)

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

closed =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Incident] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Category]
            = MAX ( 'Table'[Category] )
            &amp;&amp; 'Table'[Resolved Date] &gt;= [weekstart]
            &amp;&amp; 'Table'[Resolved Date] &lt;= [weekend]
    )
)

&nbsp;
Best RegardsMaggieCommunity Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Data modelling

4 Replies

Related content

Data Modeling

Data Modelling Issue

Data Modelling

Open the data model

DAX measures Clean-up in data model?