Split a value across 12 months/A year

[fsadiq]

Hi All,

I have a table from a SharePoint List as shown below

What i want is to split the Priority_Amount into 12 Months/1 Year as shown below by leveraging the year of any of the Urgent_date value which is basically 2024 all through.

Thank you.

[Syndicate_Admin]

You need to use the Advanced Editor to paste the code.

[Syndicate_Admin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktKFFwVNJRMjUwMABShvqG+kYGRiYgplKsDlSBE5BrgqHACKHAGcg1xlBgjFDgAlKPocBEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Select = _t, Priority_Amount = _t, Urgent_Date = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Select", type text}, {"Priority_Amount", Currency.Type}, {"Urgent_Date", type date}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..11}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",each [Urgent_Date],each Date.AddMonths([Urgent_Date],[Custom]),Replacer.ReplaceValue,{"Urgent_Date"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Priority_Amount],each [Priority_Amount]/12,Replacer.ReplaceValue,{"Priority_Amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Priority_Amount", Currency.Type}, {"Urgent_Date", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Select", "Priority_Amount", "Urgent_Date", "ID"})
in
    #"Removed Other Columns"

[fsadiq]

Thanks @Syndicate_Admin .

I tried running the query. Not sure if i applied the codes the right way but i created a blank query and pasted the codes but i rather got the below (Image)

Thank you once again.

[Syndicate_Admin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[fsadiq]

Thank you @Syndicate_Admin .

My table is as shown below:  Table1

Select	Priority_Amount	Urgent_Date	ID
Dept A	5000	1/1/2024	1
Dept B	4000	1/1/2024	2
Dept C	3000	1/1/2024	3
Dept D	2000	1/1/2024	4

And i simply would like to achieve the below (Dividing the Priority_Amount by 12 (1 Year);

Department	2024-01	2024-02	2024-03	2024-04	2024-05	2024-06	2024-07	2024-08	2024-09	2024-10	2024-11	2024-12	ID
Dept A	417	417	417	417	417	417	417	417	417	417	417	417	1
Dept B	333	333	333	333	333	333	333	333	333	333	333	333	2
Dept C	250	250	250	250	250	250	250	250	250	250	250	250	3
Dept D	167	167	167	167	167	167	167	167	167	167	167	167	4

Where i can still reference the table 1 with the newly created Table.(ID).

Thanks

[fsadiq]

Hi @Syndicate_Admin I really don't have a calendar table. I am just hoping to use the year of the Urgent_Date column. I haven't done anything yet.

Thanks 

[Syndicate_Admin]

That seems straightforward. You have a calendar table, right?  What have you tried and where are you stuck?

[fsadiq]

Hi , I was able to create a date table to capture and divide the value by 12 Months.

The issue now is that it does not filter when i make selection simply because there is no connection between the Date Table and Main Table.

i took below steps:

1. I created a date Table and added a column with the below

Priority Amount = 
var _start=DATE(2024,1,1)
var _value=[Priority]
var _end=EOMONTH(_start,11)
return IF('Date'[Date]>=_start&&'Date'[Date]<_end && DAY('Date'[Date])=1,_value/12)

Where priority is a calculated DAX measure from the below table.

Priority = sum(Unit_UrgentNeed[Priority_Amount])

I was able to get the below:

If i add a slicer, and include Title column, If i select any of the title values, it does not filter the new date table.I guess a relationship needs to be created within this 2 Tables.

Thanks in Anticipation.