HELP WITH POWER BI-Formulas DAX (scheduled weighted amount by months)

[ElenaCarciLopez]

Hello! I need some help with a POWER BI Dashboard.

I have data of: Starting date services / Number of months of our services / Weighted amount per month.

I would like to have a formula to do a visual matriz that shows:

Per year and month, from the starting date of services, the weighted amount of each month, for all the months that our services lasts: 

ex: starting date: 01/01/2024 / number of months: 3 / weighted amount per month: 2000,00 €

VISUAL:

January 2024 - 2000,00€

February 2024 - 2000,00€

Mars 2024 - 2000,00€

April 2024 - 0,00€

May 2024 -0,00€

Is it possible to show it with a formula DAX? if yes, how can I do it?

Many thanks for your help!!!

[ElenaCarciLopez · Accepted solution]

Hello thank you for your help but the it gave me a strange repartition of dates.

Finnaly I got the solution from a_andrade and it works, althought it is in Spanish I am going to copy it here: 

Para mí la mejor manera sería dividir cada una de estas líneas por cada mes para el que tenemos datos y luego usar una simple fórmula DAX para obtener los valores.

Esta es mi solución:

Empecé usando este código M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvyExW0lEKLkksKlHIT1MITi0qy0xOLQaKuZQWJZZk5ucpZOYp+ObnlWSABMNTM9MzSlJTICJKsTrRSv5paalFCoZASQNDfQMjfSMDIxMgxwiIjQ0MkJQYQZUYIisxRFFiDFVijKzECKQkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Topic", type text}, {"Start of Services", type date}, {"Duration in Months", Int64.Type}, {"Weighted Month", Int64.Type}}),
    DateList = Table.AddColumn( #"Changed Type1","Month", each List.Dates([Start of Services], [Duration in Months], #duration(31,0,0,0))),
    #"Expanded Meses" = Table.ExpandListColumn(DateList, "Month"),
    #"Calculated Start of Month" = Table.TransformColumns(#"Expanded Meses",{{"Month", Date.StartOfMonth, type date}})
in
    #"Calculated Start of Month"

Y luego usé esta fórmula DAX:

Total Month = SUM(T_Officer[Weighted Month])

Luego vinculé mi tabla de calendario a la columna Meses.

El resultado fue este:

[v-yangliu-msft]

Hi  @ElenaCarciLopez ,

Here are the steps you can follow：

1. Create date table.

Date =
var _start=MIN('Table'[Starting date services])
var _end=DATE(YEAR(_start),12,31)
var _date=
CALENDAR(
    _start,_end)
RETURN
ADDCOLUMNS(
    _date,"Month Year",FORMAT([Date],"mmmm")&" "&YEAR([Date]))

2. Create measure.

Measure =
var _year=YEAR(MAX('Table'[Starting date services]))
var _number=MIN('Table'[Number of months of our services])
return
IF(
    YEAR(MAX('Date'[Date]))=_year&&MONTH(MAX('Date'[Date]))<=3,
    MAX('Table'[Weighted amount per month.]),0)

3. The default sort method of Power bi is alphabetical, so we need to create a table..

Sort Table =
SUMMARIZE('Date',[Month Year],
"min",MINX(FILTER(ALL('Date'),'Date'[Month Year]=EARLIER('Date'[Month Year])),[Date]))

Select [Month Year] – Column tools – Sort by – [min]

4. Connect the relationship between two tables.

5. Result:

Best Regards,

Liu Yang

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