Value of collumn change by month

[Peleias]

Hi Guys
I'm studying powerbi but I couldn't come to a conclusion for the question below, I need some help to get started.
Any suggestions on how to do it, what word to look for or some tutorial.

The case, I create project overview, how much percent a task will be completed each month.

As pictured, I currently do it manually.

I create 4 pages (yellow) in powerpoint based on the table above. I copy the graphics for each page.

In PowerBI I would like to be able to click on the month (timeline?) and show me how many % would be in that month, maybe a dynamic column would be the case.

=IF(WORKDAY($E4;L$2)/$D4<0;0;IF(WORKDAY($E4;L$2)/$D4>1;1;WORKDAY($E4;L$2)/$D4))

I use for Percent in excel.

I powerpoin, manually.

In powerpoint

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @Peleias ,

According to your description, I download your sample, here's my solution.

1.Create a date table including all the date in the progress, don't make relationship between two tables.

2.Create a measure.

Progress =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 5, 22 ), MAX ( 'TP'[ May 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 6, 22 ), MAX ( 'TP'[ Jun 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 7, 22 ), MAX ( 'TP'[ Jul 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 8, 22 ), MAX ( 'TP'[ Aug 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 9, 22 ), MAX ( 'TP'[ Sep 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 10, 22 ), MAX ( 'TP'[ Oct 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 11, 22 ), MAX ( 'TP'[ Nov 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 12, 22 ), MAX ( 'TP'[ Dec 22] ),
    SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2023, 1, 23 ), MAX ( 'TP'[ Jan 23] ),
    0
)

3.Put the new date column in the timeline, the measure and project in the bar chart, get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Peleias]

Hi @v-yanjiang-msft  

Because of your measure I was able to make a lot of progress. Helped me a lot. And I'm trying to simplify in powerbi, making the excel formula inside powerbi, with ChicletSlider Is it possible? In the same style as your mensure;

I'm mentioning you here because I was only able to advance with your measurement.

=IF(WORKDAY($E4;L$2)/$D4<0;0;IF(WORKDAY($E4;L$2)/$D4>1;1;WORKDAY($E4;L$2)/$D4))

This formula uses workday in between Start Date and Date Selected (from ChicletSlicer in powerbi) and divides from Duration.

The IF of formula is for the result to be a minimum of 0 (0%) and a maximum of 1 (100%).

pbix https://drive.google.com/file/d/1A_l7kBNZ3uD8xXP5LYUbHzr1mPEz04Ac/view?usp=sharing

Expect result; 

thank you, regards

(original post https://community.powerbi.com/t5/Desktop/Mensure-Porcentage-between-two-dates-with-ChicletSlicer/m-p...)

[Peleias]

@v-yanjiang-msft  thank you very much. I was trying with relationship but still no success. I was looking at a form with unpivot data, but your method is much better.
I used ChicletSlicer with your solution and it turned out great!

Maybe is that too much to ask, is it possible to merge your solution with @amitchandak ? The percentage formula inside powerbi?
I tried here for 2h hours, but the relationship between rows and columns is something that has blown my mind.

[v-yanjiang-msft]

Hi @Peleias ,

If you want to get the correct Amik like below, you should add a new project column, PROJECT E1,PROJECT E2 and PROJECT E3 belong to one project, the same goes for R and T. 

In Power Query it's easy to get it by below steps:

#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Project", "Project - Copy"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Project - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Project - Copy.1", "Project - Copy.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Project - Copy.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Project - Copy.1", "ProjectGroup"}})

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

[Peleias]

@amitchandak 

Thanks Amit, I already subscribed to your channel. But I don't think I got it with formula.
https://drive.google.com/file/d/1NRL8bp_rFSejJGA523nEGyfH8F_ZAXyJ/view?usp=sharing

[amitchandak]

@Peleias , Try a measure like

divide(Sum(Table[Duration]), calculate(Sum(Table[Duration]), filter(allselected(Table) , Table[Project] = max(Table[Project]))))

Percent of SubTotal or Total: https://www.youtube.com/watch?v=6jTildcV2ho&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=37