Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andrebsilva
New Member

Sum SQM by the contract with stard and end date

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

3 REPLIES 3
andrebsilva1
Regular Visitor

@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

 

andrebsilva1_3-1680292161503.png

The measeure

CumulativeSQM =
VAR MaxDate = MAX ('Calendar'[Date])
VAR MinDate = MIN ('Calendar'[Date])
RETURN
Calculate (
    sum(Floor_Level_Data[Contracted Sq M]),
        Floor_Level_Data[OCD] <= MaxDate,
        Floor_Level_Data[LED] >= MinDate
)

 

 

Q3 of FY21, presente a total that not exist 1.667,63.

andrebsilva1_1-1680291735845.pngandrebsilva1_2-1680291772522.png

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.

tamerj1
Super User
Super 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]
)

andrebsilva
New Member

@tamerj1 @amitchandak @Greg_Deckler 
Please, could you help me?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors