Running Total Issue with DAX in Power BI (No Direct Relationship Between Tables)

[Madhans]

Hi everyone,

I’m working on a requirement to calculate the running total for the past 6 months in Power BI. I have a DAX measure called Average_Vessels and I need to calculate the running total for the previous month.

Here’s the issue:

• When I calculate the running total using DatesInPeriod() in the VAR A or VAR B, the calculation works fine individually.
• However, when I attempt to divide the result (as part of a more complex calculation), the running total does not behave as expected.

Key points:

• There is no direct relationship between the IMO_MASTER_LIST table and the Calendar table in my model.
• The error occurs only when performing the division part of the measure. When running only the calculation without division, the running total appears correct.

Questions:

1. Can anyone suggest why the running total works when calculated separately but not after division?
2. How can I resolve this issue given there’s no direct relationship between the IMO_MASTER_LIST and Calendar tables?
3. Any suggestions on how to properly calculate the running total for the last 6 months in this scenario?
   
   Looking forward to your insights!

[v-xinruzhu-msft · Accepted solution]

Hi @Madhans 

Based on your description, you can use crossfilter() function in your measure, you can try the following.

Measure =
CALCULATE (
    [Measure],
    CROSSFILTER ( IMO_MASTER_LIST[Column], VESSEL_OFFHIRE_HOUR_SMPL[Column], BOTH )
)

You can refer to the following link to know more about the crossfilter() function.

CROSSFILTER function - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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