Can't figure out DEX logic

[Gaurav_84]

Hi Experts,

I am looking for DAX logic for one of my scenario. As per my business requirement we need to identify number of houses in construction phase. A house can have multiple construction phase before it handover to client example brickwork, Fixture & fittings, slab work, frame construction etc.. & finally handover to client. 

Now as per data is recieved from business is in below format.

House - ABC

Start Date - 22/05/2019

Slab - 06/06/2019

Frame - 18/06/2019

Fixture & fitting - 07/08/2019

Brickwork - 15/10/2019

Handover - 20/02/2020

Now as per my requirement i need to create bar chart where i need to count this house in all months till 20.04.2020.

As per buisiness case house will remain in construction before it handed over to client in Feb2020. so i need to count this house in all months from June 2019 to Feb2020.

Regards

[v-lionel-msft]

Hi @Gaurav_84 ,

Is your data table similar to this?

Best regards,
Lionel Chen

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

[Gaurav_84]

Yes its somewhat similar. But date is always not there.

 As shown below 

Job A will need to count every month till it reaches to handover i.e from May 2019 to Dec 2019. Job B will count every month from July to future months as there is no handover yet. Same in case of Job C.

regards

[v-lionel-msft]

Hi @Gaurav_84 ,

Like this?

Or Like this?

Please tell me what fields need to be added to each label of the bar chart.

Best regards,
Lionel Chen

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

[Gaurav_84]

Hi @v-lionel-msft 

Report should count the number of jobs each month between start date & handover date. For example in below table Job A0001 started in month in month of May 2019 & Handover in month of Jan 2020. so i need to count this job evermonth from May 2019 to Dec 2019 since its handover in Jan2020 i dont want to count it in Jan2020. Same goes for Job B0001 it start in July 2019 & there is no hanover yet so it will count till current month Aug 2020. so if you see in month of July i dont have anything for job A0001 but still report need to count it. so my count for month of july should be "3" (A0001+B0001+C0001). 

This Bar graph will help you it need to show value for each month for example 

regards

Gaurav

[Greg_Deckler]

@Gaurav_84 - Not to be a pest, but this sounds like Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

The purpose of Open Tickets is to count things for the entire interval from when they first appear to when they are closed. 

[Gaurav_84]

Hi @Greg_Deckler ,

Your Query is almost working for me is it possible to exclude the month of finished date. For eaxmple start date 01.04.2019 & finished date is 01.02.2020. can we count job from April 2019 to Jan 2020.

Regards

Gaurav

[Greg_Deckler · Accepted solution]

@Gaurav_84 - Wouldn't that just be:

Tickets Open = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Opened Date] &&
        [Date] < [Effective Date]
    ),
    "ID",[Ticket Num],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)

[amitchandak]

@Gaurav_84 , Refer to this blog. This deal with two date but you have more. You have to create various stages in progress using date table

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

[Greg_Deckler]

@Gaurav_84 - Seems like you want something along the lines of Open Tickets - https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Gaurav_84]

Thanks Greg,

your solution somewhat works for me but its not giving the correct value for few Houses i am getting correct data & for few its not working. It still showing me value for some project in month of august which was closed in month of march.

Regards

[pranit828]

Hi @Gaurav_84 

You need to provide more details as sample of the datastructure input, how your columns are laid out and what you need in the output.