Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!!
Solved! Go to 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:
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:
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
70 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |