Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I need a help to create a measure to sum a sqm by contract accoding the contract term
I have a contract base and create a calendar table with calendarauto
Contract | Start Date | End Date | SQM |
BRA0001 | 01/01/2015 | 12/31/2018 | 6.000,00 |
BRA0001 | 01/01/2019 | 12/31/2021 | 2.500,00 |
BRA0002 | 01/07/2016 | 06/30/2020 | 1.500,00 |
I expect to present the information like this
FY | Q1 | Q2 | Q3 | Q4 |
2015 | 6.000,00 | 6.000,00 | 6.000,00 | 6.000,00 |
2016 | 6.000,00 | 6.000,00 | 7.500,00 | 7.500,00 |
2017 | 7.500,00 | 7.500,00 | 7.500,00 | 7.500,00 |
2018 | 7.500,00 | 7.500,00 | 7.500,00 | 7.500,00 |
2019 | 4.000,00 | 4.000,00 | 4.000,00 | 4.000,00 |
2020 | 4.000,00 | 4.000,00 | 1.500,00 | 1.500,00 |
2021 | 1.500,00 | 1.500,00 | 1.500,00 | 1.500,00 |
2022 | 0 | 0 | 0 | 0 |
Best Regards
@tamerj1
Thanks a lot for your answer.
The solution works, but now I have another probleme.
The sum by month or quarter not works well, when the total of sqm change in the middle of month
Table fato
The measeure
Q3 of FY21, presente a total that not exist 1.667,63.
I try to resolve change sumx by calculete, but not sucess until now.
Note my fiscal year star on setember.
Best Regards,
André
Notes - I don't now my first user not works, so I create a new user.
Hi @andrebsilva
Please try
=
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
MIN ( 'Date'[Date] )
RETURN
SUMX (
FILTER (
'Table',
'Table'[Start Date] <= MaxDate
&& 'Table'[End Date] >= MinDate
),
'Table'[SQM]
)
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |