Problem Power BI : merge period in table...

[sebsenciel]

Hello to all,

I can't do the following under powerbi 😞
Can you help me please ?
I will try to detail my problem as clear as possible 🙂

My data model is simple: I have only one table, namely the PROJECT table (see attached diagram)

this table has two columns :
- date column
- project tasks column (values T1 and T2)

Each task is associated with a date.

Ok.

This project was carried out over a period of time between a min and max date
The min date of the project : in green
the max date of the project in red

Target : for each task, I would like to associate the period of the corresponding project, indicating for each date of the period if there was an associated task (yes ==> 1, no ==> 0).
See target on the right !

I tried to merge with a calendar table, but I couldn't do it.

Thanks for your help
Sébastien

[Anonymous · Accepted solution]

Hi  @sebsenciel ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

 Table = GENERATE(VALUES(Project[Tache]),VALUES(Calendrier[Calendrier Project])) 

(3) We can create a calculated column.

Column = var a=FILTER(Project,[Tache]=EARLIER('Table'[Tache])) var b=SUMMARIZE(a,[Date]) return IF([Calendrier Project] in b,1,0) 

(4) Then the result is as follows.

Best Regards,

Neeko Tang

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

[sebsenciel]

Hello v-tangjie-msft,

Thank's a lot for your answer.

very NICE !!!!

have a great holiday season 😉

Best regards

Sebastien

[sebsenciel]

Hi amitchandak,

thank you for your feedback
I will look at your answer and get back to you if I have any questions,
Thank you

Sébastien

[amitchandak]

@sebsenciel , Try a measure like below with date table

calculate(Sumx(filter(Date, Date[Date] >= Minx(filter(allselected(Table), Table[Tache] = max(Table[Tache])), Table[Date]) && Date[Date] <= Maxx(filter(allselected(Table), Table[Tache] = max(Table[Tache])), Table[Date])), if(Date[Date] = Max(Table[Tache]) ,1,0)))