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

Get 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

Reply
daytek51
Regular Visitor

Cumulative measure when using both a Date Dimension and Time Dimension

I'm trying to create a cumulative measure for delivered tons in my model. I have a date dimension and a time dimension as we need it down to the hour. The issue I'm having is I can't figure out how to properly write the dax to cumulate across days, when the time dimension(hour) is placed. I need it to increment with each hour, but once it resets to hour 1 then next day, it also resets the cumulative tons. 

The measure needs to filter out loads that haven't yet been delivered which I have created a hour table with 25 values 24 being the null value in this case: 


daytek51_0-1701969782212.png

 



This is the measure: 

Cumulative Tons =

VAR _datefilter =  FILTER(
        ALLSELECTED(DateDIm[Date]),
        DateDIm[Date] <= MAX(DateDIm[Date])
    )
VAR _hourfilter = FILTER(
        ALLSELECTED(HourTable[Hour]),
        HourTable[Hour] <= MAX(HourTable[Hour])
    )
VAR _completedfilter = CALCULATETABLE(
    cr7e8_dauberloads,
    _datefilter,
    _hourfilter,
    cr7e8_dauberloads[Hour] < 24  -- filter out loads that have not yet been delivered
)
RETURN
CALCULATE(
    SUM(cr7e8_dauberloads[deliveredTons]),
    _completedfilter,
    VALUES(stg_orders[dropOffLocation_name])
)
 
This is the results I'm currently getting but need it to keep summing across days 
daytek51_0-1701970191893.png

 

Any help would be greatly appreciated.
1 REPLY 1
lbendlin
Super User
Super User

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

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to 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...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors