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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
HopeThomas456
Frequent Visitor

Sum of Area that is active between start and end dates

Hi! I am working on summing area for all area that is currently active for each month. Can someone take a look at my DAX code and see where I can fix it or any  pointers? 

 

TotalRollingArea =
CALCULATE(
    SUM('Join Data'[Area]),
    FILTER(
        'CalendarDates',
        'CalendarDates'[Date] >= (MIN('Join Data'[Allocation Data.Planned Start Date])
        && 'CalendarDates'[Date] <= (MAX('Join Data'[Allocation Data.Planned End Date]))))
3 REPLIES 3
FreemanZ
Super User
Super User

hi @HopeThomas456 ,

 

that looks like a measure. How are you going to present the measure, or what context do you have for this measure?

 

If you visualize it with a column from CalendarDates table, most probably you might need to wrap an ALL, like:

TotalRollingArea =
CALCULATE(
    SUM('Join Data'[Area]),
    FILTER(
        ALL('CalendarDates'),
        'CalendarDates'[Date] >= (MIN('Join Data'[Allocation Data.Planned Start Date])
        && 'CalendarDates'[Date] <= (MAX('Join Data'[Allocation Data.Planned End Date]))))

Hi @FreemanZ , 

 

I have tried different DAX codes to try to sum area that is active for the duration of the start and end date and running into some issues. Here is a sample of what my data looks like. Some of my data do not have an end dates so that would indicated that that area is always active. I have changed the date formate from Date/Time/Zone to just Date. Looking to create a stacked bar chart for each month and each bar show the different categories. I can add a slicer so that the chart will filter based on program and shop. I created a basic Calendar that has days, months, quarters, years. 

HopeThomas456_0-1699629316061.png

I have tried the following DAX: 

RollingArea =
CALCULATE(
    SUMX('Join Data', COUNTROWS(CalendarDates) * 'Join Data'[Area]/(1+DATEDIFF('Join Data'[Allocation Data.Planned Start Date],'Join Data'[Allocation Data.Planned End Date],day))),
    FILTER(
        'Join Data',
        'Join Data'[Allocation Data.Planned Start Date]<=MAX(CalendarDates[Date])
        && 'Join Data'[Allocation Data.Planned End Date]>=MIN(CalendarDates[Date])
       
))
 
TotalRollingArea =
CALCULATE(
    SUM('Join Data'[Area]),
    FILTER(
        ALL('CalendarDates'),
        'CalendarDates'[Date]>= (MIN('Join Data'[Allocation Data.Planned Start Date])
        && 'CalendarDates'[Date]<=(max('Join Data'[Allocation Data.Planned End Date]))))
 
TotalRollingSum =
CALCULATE(
    SUM('Join Data'[Area]),
    FILTER(
        'Join Data',
        'Join Data'[Allocation Data.Planned Start Date] >= (MIN('CalendarDates'[Date]) && 'Join Data'[Allocation Data.Planned End Date] <= (MAX('CalendarDates'[Date])))
))
 
The following codes either give me an error or not the correct represenation of data. Is there anything I should be doing instead? 

Another note that I need guidance on is that if a month isn't present in the Join data I still want it to be present in the stacked bar chart. Any help with this? 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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