March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Dear good afternoon I want to ask for your help to be able to perform the following measure or generacy to achieve the following
I have the following data
enterprise | Date ini | End date | Monthly Value |
A | 01/01/2022 | 31/12/2024 | 4 |
A | 01/01/2021 | 31/12/2023 | 3 |
A | 01/04/2020 | 30/06/2020 | 2 |
B | 01/01/2020 | 31/03/2020 | 6 |
B | 01/01/2021 | 31/03/2021 | 5 |
And I need to get this:
YEAR | Sum value of the year depending on the number of months |
2020 | 24 |
2021 | 51 |
2022 | 84 |
2023 | 84 |
2024 | 48 |
where you have to add depending on the year and number of months that represents in that year the monthly value
Thanks in advance
Hi @manv23061997
Can you explain a little biut better what you want to achieve? Not understanding what means the Sum value of the year depending on the number of months.
How do you get 24 for 2020 and 51 for 2021.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIf I tell you
Result 24 for 2020 is obtained as follows:
In the box I can see that there is a record of the company "A" that has as its start date: 01/04/2020 and final file: 30/06/2020 between that range of dates there are 3 months of difference, therefore 3 months multiplied by the monthly value 2 gives me 6 as a result in the year 2020, then there is also a record "B" that has as a start date: 01/01/2020 and end date: 31/03/2020 Between those dates there is 3 months of difference multiplied by the monthly value 6 results in 18.
So in the year 2020 I have as a total value 6 + 18 = 24
Result 51 for the year 2021 is obtained in the same way only considering the total value of the records that have included the year within the period of start date and end date.
Hi @mavn23061997 ,
I assume the years table is separated from the other table, create the following measure:
Values per Month =
VAR temptable =
ADDCOLUMNS (
FILTER (
ADDCOLUMNS (
CROSSJOIN (
'Years_Table',
ADDCOLUMNS (
'DataValues',
"StartYear", YEAR ( 'DataValues'[Date ini] ),
"EndYear", YEAR ( 'DataValues'[End date] )
)
),
"Flag",
IF ( [StartYear] <= [Years] && [EndYear] >= [Years], 1, 0 )
),
[Flag] = 1
),
"TotalMonths",
SWITCH (
TRUE (),
[StartYear] = [Years],
DATEDIFF (
'DataValues'[Date ini],
MIN ( DATE ( [years], 12, 31 ), 'DataValues'[End date] ),
MONTH
) + 1,
[StartYear] > [Years], DATEDIFF ( DATE ( YEAR ( [StartYear] ), 12, 31 ) + 1, 'DataValues'[Date ini], MONTH ),
[StartYear] < [Years]
&& [EndYear] > [Years], DATEDIFF ( DATE ( [Years], 1, 1 ), DATE ( [Years], 12, 31 ), MONTH ) + 1,
[EndYear] = [Years], DATEDIFF ( DATE ( [years], 1, 1 ), 'DataValues'[End date], MONTH ) + 1
)
)
RETURN
SUMX ( tempTable, [TotalMonths] * 'DataValues'[Monthly Value] )
Now just use this measure with the years column:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI just tried it and it works, just out of curiosity if in case I had a calendar table related to start date and end date, how could the same result be achieved
This depends on how they are related.
What is the active relationship?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsStart date
Try the following formula:
Values per Month =
VAR temptable =
ADDCOLUMNS (
FILTER (
ADDCOLUMNS (
CROSSJOIN (
DISTINCT('Calendar'[Year]),
ADDCOLUMNS (
'DataValues',
"StartYear", YEAR ( 'DataValues'[Date ini] ),
"EndYear", YEAR ( 'DataValues'[End date] )
)
),
"Flag",
IF ( [StartYear] <= [Year] && [EndYear] >= [Year], 1, 0 )
),
[Flag] = 1
),
"TotalMonths",
SWITCH (
TRUE (),
[StartYear] = [Year],
DATEDIFF (
'DataValues'[Date ini],
MIN ( DATE ( [year], 12, 31 ), 'DataValues'[End date] ),
MONTH
) + 1,
[StartYear] > [Year], DATEDIFF ( DATE ( YEAR ( [StartYear] ), 12, 31 ) + 1, 'DataValues'[Date ini], MONTH ),
[StartYear] < [Year]
&& [EndYear] > [Year], DATEDIFF ( DATE ( [Year], 1, 1 ), DATE ( [Year], 12, 31 ), MONTH ) + 1,
[EndYear] = [Year], DATEDIFF ( DATE ( [year], 1, 1 ), 'DataValues'[End date], MONTH ) + 1
)
)
RETURN
SUMX ( tempTable, [TotalMonths] * 'DataValues'[Monthly Value] )
Don't forget to accept the correct answer to help others.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
122 | |
97 | |
89 | |
76 | |
69 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |