Forum Discussion

fsadiq's avatar
fsadiq
Frequent Visitor
2 years ago

Split a value across 12 months/A year

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.

 

Need Help

8 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

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

    • fsadiq's avatar
      fsadiq
      Frequent Visitor
      2 years ago

      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.

  • fsadiq's avatar
    fsadiq
    Frequent Visitor
    2 years ago

    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's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    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-Forum/ba-p/963216
    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/1447523

    • fsadiq's avatar
      fsadiq
      Frequent Visitor
      2 years ago

      Thank you Syndicate_Admin .

      My table is as shown below:  Table1

      SelectPriority_AmountUrgent_DateID
      Dept A50001/1/20241
      Dept B40001/1/20242
      Dept C30001/1/20243
      Dept D20001/1/20244

       

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

      Department2024-012024-022024-032024-042024-052024-062024-072024-082024-092024-102024-112024-12ID
      Dept A4174174174174174174174174174174174171
      Dept B3333333333333333333333333333333333332
      Dept C2502502502502502502502502502502502503
      Dept D1671671671671671671671671671671671674

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

       

      Thanks

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago
    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's avatar
      fsadiq
      Frequent Visitor
      2 years ago

      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.