Data not rolling up correctly with date columns

[dcormiernj]

I have a matric visual with date, month, qtr ,year as the columns

and the rows are group / category / job / hours

i have the matrix working fine with all of the correct numbers and all totals when i have the matrix expanded down to the column date level.

I am also just showing the numbers as weekly numbers and have filtered so it only displays weekday=sunday

but when i roll up to see the monthly totals or the qtrerly totals or the yearly totals it is not summing correctly.  or not at all. 

for example, when i rollup to month level i would expect it to total all of the 4 weekly numbers and give me the monthly total.  same for qtr and year.  I have done this many times with other visuals. 

but this time the month rollup for example seems to be displaying the last number of the month which is the total of the 4th week.

how do i get this to roll up correctly ?  any idea???  thank you

[dcormiernj]

Thank you All

[dcormiernj]

your question is hard for me to answer.  but im wondering if I am layering too many measures to get this value that i want to rollup.  an example of the matrix rows are category > size > job.  the columns are year > qtr > month > date

the date has a hard filter of just sundays.  

the value for this matrix is is the multiplication of an hours measure * a rate for example.  but the hours measures are several and have maybe a combination that makes this visual not work.  i have copied the measures here for your info.   

the value I am pulling in is can be called  "Total amount"

"Total amount" = Total FC PM Hrs ALL = [FCPS Tot Hrs Final] + [Forecast Tot Hrs Final]

Total FC PM Hrs All = [FCPS Tot Hrs Final] + [Forecast Tot Hrs Final]

FCPS Tot Hrs Final = if(HASONEFILTER('Forecast Pipeline'[xx Job#]),[FCPS Total Hrs Part 1],SUMX(VALUES('Forecast Pipeline'[xx Job#]),[FCPS Total Hrs Part 1]))

FCPS Total Hrs Part 1 = [FCPS P1 Hours 2024only]+[FCPS P2 Hours 2024only]+[FCPS P3 Hours 2024only]+[FCPS P4 Hours 2024only]

FCPS P4 Hours 2024only = If( SELECTEDVALUE('Forecast Pipeline'[P4 Start Date])<DATE(2024,01,01),0,[FC Pre-Sign P4 Hours])

FC Pre-Sign P4 Hours = if(AND([MIN P4 Start Date]<MAX('xx-Calendar'[Date]),SELECTEDVALUE('Forecast Pipeline'[End Date])>=MAX('xx-Calendar'[Date])),[FCPS P4 Wkly Hours],0)

FCPS P4 Wkly Hours = sumx('Forecast Pipeline','Forecast Pipeline'[P4 Wkly Hrs-PM])

Forecast Tot Hrs Final = if(HASONEFILTER('xx Project List'[xx WO#]),[Forecast Total Hrs Part 1],SUMX(VALUES('xx Project List'[xx WO#]),[Forecast Total Hrs Part 1]))

Forecast Total Hrs Part 1 = [ForeCast P1 Hours 2024only]+[ForeCast P2 Hours 2024only]+[ForeCast P3 Hours 2024only]+[ForeCast P4 Hours 2024only]

Forecast Total Hrs Part 1 = [ForeCast P1 Hours 2024only]+[ForeCast P2 Hours 2024only]+[ForeCast P3 Hours 2024only]+[ForeCast P4 Hours 2024only]

ForeCast P4 Hours 2024only = If( SELECTEDVALUE('xx Project List'[P4 Start Date])<DATE(2024,01,01),0,[ForeCast P4 Hours])

ForeCast P4 Hours = if(AND(SELECTEDVALUE('xx Project List'[P4 Start Date])<=MAX('xx-Calendar'[Date]),SELECTEDVALUE('xx Project List'[End Date])>MAX('xx-Calendar'[Date])),[P4 Wkly Hours],0)

probably too much information.  but I just cant figure out how to make this total amount rollup to month/qtr etc.

it rolls up fine for the row values

thanks for your help

[v-aatheeque · Accepted solution]

Hi @dcormiernj 

Thank you for providing the details about your matrix visual setup. It sounds like you're facing an issue with the aggregation of weekly numbers when rolling up to monthly, quarterly, and yearly totals. Here are some steps and considerations to help you resolve:

Ensure that the measure for hours is set to aggregate correctly. It should be using the "Sum" function to total the hours across the weeks. If you're using a tool like Power BI.verify that the measure is defined as:

measure :

Total Hours = SUM('fact Table'[Hours])

If your date field is part of a hierarchy (Year > Quarter > Month > Week > Day), make sure that the hierarchy is being utilized correctly in the matrix.

Since you have a filter applied to show only Sundays, ensure that this filter does not interfere with the aggregation when rolling up to higher levels.

measure :

TotalSundayHoursFixed = 
CALCULATE(
    SUM('fact Table'[Hours]),
    KEEPFILTERS('Data table'[Weekday] = "Sunday")
)

 Verify that the relationships between your date table and the fact table (where your hours data is stored) are correctly established. The date table should have a one-to-many relationship with the fact table.

If you've made changes to your data model or measures, make sure to refresh the data in your visualization tool to see the updated results.

Expected Output :

for your better understanding I am attaching the sample PBIX file.
Hope this helps !

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[Deku]

Do you have a explict measure or implict measure? If implict check you have the aggregation set appropriately to sum, max etc as per what you want to see and not first or last. If you don't see sum etc then check your numbers are a numberic data type and not strings