Combining columns in a single measure separated by date

[MPietrzyk]

Hello,

I do not think it's possible to do the way I want it, but I think I'd at least ask, as I cannot find a way to do it myself.

I want to calculate days spent on tasks per month. During the year there was a change in how those days are measured. Previously there was a single value that would encompass all days spent on a task. Now we have 12 values, each corresponding to days spent in a given month so that we could track days better. I also have start and end date.

Example for old and new data:

ID	Start Day	End Day	Days spent old	Days spent January	Days spent February	Days spent March	Days spent April	Days spent May	Days spent June	Days spent July	Days spent August	Days spent September	Days spent October	Days spent November	Days spent December
1	01.03.2022	30.04.2022	10	0	0	0	0	0	0	0	0	0	0	0	0
2	04.05.2022	27.06.2022	0	0	0	0	0	7	3	0	0	0	0	0	0

For both tasks we know that someone has spent 10 days on it, but for the first case we don't know if it was 10 days in March or in April or what's the split. In this case we would assign all of the days to the month that the End date falls into.

In the second case we know the split between the months.

Is there a way to create a measure that would sum up all the days per month from both old system and new that we could visualize as days spent per month bar graph?

[Anonymous · Accepted solution]

Hi @MPietrzyk ,

Unpivot the [Days spent Months] columns in Query Editor.

Then create two calculated columns.

months = RIGHT('Table'[Attribute],LEN('Table'[Attribute])-11)

end_month = 'Table'[End day].[Month]

Result:

Then create a measure and create the visual:

Measure = SUM('Table'[Value])+CALCULATE(MAX('Table'[Days spent]),FILTER('Table','Table'[end_month]=SELECTEDVALUE('Table'[months])))

Best Regards,

Jay

[MPietrzyk]

Hi @Anonymous

thanks for the reply. It really helped me figure it out. What I did based on your suggestion is:

1. Unpivot [Days spent Months] columns making sure that I switch all nulls to 0

2. Created months column

months = SWITCH(MID('Table'[Attribute],11,3),"Jan", 1,"Feb", 2,"Mar", 3,"Apr", 4,"May", 5,"Jun", 6,"Jul", 7,"Aug", 8,"Sep", 9,"Oct", 10,"Nov", 11,"Dec", 12)

For some reason RIGHT did not work for me.

Then I've created a measure

Time_Spent = calculate(SUM('Table'[Value]),'ONT Cases PowerBI Dashboard'[months])+SUMX(DISTINCT('Table'[ID]), FIRSTNONBLANK('Table'[Days spent], 0))

I've changed it as above, because your measure summed by End Date only, even if the record had some days in [Days spent Months] columns

[MPietrzyk]

Hey @Greg_Deckler 

In the main post I've included an example of old and new data, but let me show what I would mean to do on some more examples.

Data consists of tasks with ID, Start Day, End Day, and 13 columns regarding the days it took to complete a task. Days Spent is an old measure that only keeps the value of the days and we have no idea if those days were split between months or not so we use End Day to decide. I.e. Days Spent is 5 and End Day is 06.06.2022 -> that means that the task took 5 days in June (even though in real life it might have been 4 days in May and 1 in June). Rest of the columns will be empty in this case

New data has the days split between other 12 columns, each for seperate month, and 0 in Days Spent as it's an old measure. I.e. Start and End Day don't matter anymore we only look at for example 4 days in Days Spent May and 1 Days Spent June.

Data table might look like this:

ID	Start day	End day	Days spent	Days spent January	Days spent February	Days spent March	Days spent April	Days spent May	Days spent June	Days spent July	Days spent August	Days spent September	Days spent October	Days spent November	Days spent December
1	02.02.2021	02.03.2021	5	0	0	0	0	0	0	0	0	0	0	0	0
2	08.02.2021	19.04.2021	5	0	0	0	0	0	0	0	0	0	0	0	0
3	01.03.2021	12.09.2021	9	0	0	0	0	0	0	0	0	0	0	0	0
4	16.03.2021	18.07.2021	4	0	0	0	0	0	0	0	0	0	0	0	0
5	05.06.2021	18.09.2021	2	0	0	0	0	0	0	0	0	0	0	0	0
6	29.09.2021	12.12.2021	0	0	0	0	0	0	0	0	0	0	0	3	4
7	14.10.2021	24.12.2021	0	0	0	0	0	0	0	0	0	0	1	6	0
8	05.11.2021	09.11.2021	0	0	0	0	0	0	0	0	0	0	0	4	0
9	06.12.2021	26.12.2021	0	0	0	0	0	0	0	0	0	0	0	0	10
10	16.12.2021	18.12.2021	0	0	0	0	0	0	0	0	0	0	0	0	1

I want to create a measure that will combine old and new days spent measure in a single measure that I can plot per month. The result for the data in the table would look like this:

[Greg_Deckler]

@MPietrzyk Probably possible but would need to understand the source data better. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.