SUMIF in power BI - Not sure if this is the right DAX to use for a measure.

[bbass82]

Hi All,

In need of some help, NEW to power BI (about 3 months now) and I am struggling to find the proper way to build a measure for a matrix I am trying to setup for Shipment Window. Essentially I have 2 different sources of shipments (FOB = Import / DOM = Domestic) and I want to roll up specific monthly $ values in 2023/2024 for each source by season (Spring = S24 and Fall = F24).

Essentially, I am looking to roll-up as per the below to get the shipment $ per season as follows:

S24 Measure: that would include all Shipments $ for DOM Source (DEC 2023 to MAY 2024) + Shipment $ for FOB Source from (NOV 2023 TO APR 2024) 

F24 Measure: that would include all Shipments $ for DOM Source (JUN 2024 to NOV 2024) + Shipment $ for FOB Source from (MAY 2024 TO OCT 2024)

Any help would be appreciated. 

Thank-you for your time,

Brandon 

[v-heq-msft · Accepted solution]

Hi @bbass82 ,
According to your description, you can use calulate+filter to filter the conditions and perform the calculations. For example

S24 Measure = 
CALCULATE(
    SUM('Shipments'[Shipment $]),
    (
        ('Shipments'[Source] = "DOM" && 'Shipments'[Date] >= DATE(2023, 12, 1) && 'Shipments'[Date] <= DATE(2024, 5, 31)) ||
        ('Shipments'[Source] = "FOB" && 'Shipments'[Date] >= DATE(2023, 11, 1) && 'Shipments'[Date] <= DATE(2024, 4, 30))
    )
)

F24 Measure = 
CALCULATE(
    SUM('Shipments'[Shipment $]),
    (
        ('Shipments'[Source] = "DOM" && 'Shipments'[Date] >= DATE(2024, 6, 1) && 'Shipments'[Date] <= DATE(2024, 11, 30)) ||
        ('Shipments'[Source] = "FOB" && 'Shipments'[Date] >= DATE(2024, 5, 1) && 'Shipments'[Date] <= DATE(2024, 10, 31))
    )
)

Hopefully, the above logic can help you, and if you have any other questions, as lbendlin  said, you can provide the complete sample data so that we can help you faster. Please hide sensitive information in advance.

Best regards,
Albert He

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

[Kedar_Pande · Accepted solution]

@bbass82 

Create the Measures

S24 Measure = 
VAR DOM_S24 = 
CALCULATE(
SUM('Shipments'[Shipment $]),
'Shipments'[Source] = "DOM",
'Shipments'[Shipment Date] >= DATE(2023, 12, 1),
'Shipments'[Shipment Date] <= DATE(2024, 5, 31)
)

VAR FOB_S24 = 
CALCULATE(
SUM('Shipments'[Shipment $]),
'Shipments'[Source] = "FOB",
'Shipments'[Shipment Date] >= DATE(2023, 11, 1),
'Shipments'[Shipment Date] <= DATE(2024, 4, 30)
)

RETURN
DOM_S24 + FOB_S24

F24 Measure = 
VAR DOM_F24 = 
CALCULATE(
SUM('Shipments'[Shipment $]),
'Shipments'[Source] = "DOM",
'Shipments'[Shipment Date] >= DATE(2024, 6, 1),
'Shipments'[Shipment Date] <= DATE(2024, 11, 30)
)

VAR FOB_F24 = 
CALCULATE(
SUM('Shipments'[Shipment $]),
'Shipments'[Source] = "FOB",
'Shipments'[Shipment Date] >= DATE(2024, 5, 1),
'Shipments'[Shipment Date] <= DATE(2024, 10, 31)
)

RETURN
DOM_F24 + FOB_F24

Add the measures S24 Measure and F24 Measure to your visuals to see the seasonal roll-up values.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[bbass82]

Kedar! Thank-you so much for this DAX solution. It worked perfectly for what we wanted to build for our shipment rollup. Appreciate you more than words can say. All the best and look forward to your advice in the future. 

[v-heq-msft · Accepted solution]

Hi @bbass82 ,
According to your description, you can use calulate+filter to filter the conditions and perform the calculations. For example

S24 Measure = 
CALCULATE(
    SUM('Shipments'[Shipment $]),
    (
        ('Shipments'[Source] = "DOM" && 'Shipments'[Date] >= DATE(2023, 12, 1) && 'Shipments'[Date] <= DATE(2024, 5, 31)) ||
        ('Shipments'[Source] = "FOB" && 'Shipments'[Date] >= DATE(2023, 11, 1) && 'Shipments'[Date] <= DATE(2024, 4, 30))
    )
)

F24 Measure = 
CALCULATE(
    SUM('Shipments'[Shipment $]),
    (
        ('Shipments'[Source] = "DOM" && 'Shipments'[Date] >= DATE(2024, 6, 1) && 'Shipments'[Date] <= DATE(2024, 11, 30)) ||
        ('Shipments'[Source] = "FOB" && 'Shipments'[Date] >= DATE(2024, 5, 1) && 'Shipments'[Date] <= DATE(2024, 10, 31))
    )
)

Hopefully, the above logic can help you, and if you have any other questions, as lbendlin  said, you can provide the complete sample data so that we can help you faster. Please hide sensitive information in advance.

Best regards,
Albert He

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...