Forum Discussion

OldDinosaur79's avatar
OldDinosaur79
Frequent Visitor
1 year ago
Solved

Using Hierarchy Date - Totalling issue

I am completely new to Power BI and have a query that works out Incoming by looking at (Open in Day + Closed in Day) minus Open from previous day (see DAX below).

The issue I have is when using the date hierarchy at a daily level my volumes are correct but the monthly and yearly totals are incorrect.  


Any help would be appreciated

 

--Get Open and Closed

VAR CurrentOpen =

Calculate(SUM(Table[Volume]),Table[Process] = "Open")

 

VAR CurrentClosed =

Calculate(SUM(Table[Volume]),Table[Process] = "Closed")

 

-- Previous Day Open

VAR PreviousOpen =

VAR latestdate = max(dim_calender[Date])

VAR previousdate = Calculate(max(dim_calender[Date]),dim_calender[Date])<latestdate)

 

return Calculate(sum(Table[volume]),dim_calender[Date]=previousdate,table[Process]="Open")

 

--Calculate Volume

Return

(CurrentOpen+CurrentClosed)-PreviousOpen

 

Need Help
  • DataZoe's avatar
    DataZoe
    1 year ago

    Hi OldDinosaur79 

     

    I could not get your measure to work as you had pasted it, but depending on how you are trying to see it aggregated by your date hierarchy, you may need to use SUMX, MINX, MAXX, or AVERAGEX. These will perform the calculation at the daily level then sum, min, max, or average the daily calculation to other date periods. 

     

    Here is a measure I got working that will rollup the daily number as a sum for month, quarter, year, etc.

     

     

    Incoming = 
        // The measure adds the volumes for "Open" and "Closed" processes and subtracts the volume for "Open" processes from the previous day.
        // SUMX dim_calendar will do the daily calculation then sum it by other periods (month, quarter, year). 
        // You can also use MINX (lowest incoming that period), MAXX (highest incoming that period), or AVERAGEX (average incoming that period)
        SUMX(
			dim_calender,
			CALCULATE(
				SUM('Table'[Volume]),
				'Table'[Process] = "Open"
			) +
			CALCULATE(
				SUM('Table'[Volume]),
				'Table'[Process] = "Closed"
			)
			- CALCULATE(
				SUM('Table'[Volume]),
				'Table'[Process] = "Open",
				PREVIOUSDAY(dim_calender[Date])
			)
		)

     

     

    More information about SUMX: SUMX function (DAX) - DAX | Microsoft Learn

     

3 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    1 year ago

    Hi OldDinosaur79 

     

    I could not get your measure to work as you had pasted it, but depending on how you are trying to see it aggregated by your date hierarchy, you may need to use SUMX, MINX, MAXX, or AVERAGEX. These will perform the calculation at the daily level then sum, min, max, or average the daily calculation to other date periods. 

     

    Here is a measure I got working that will rollup the daily number as a sum for month, quarter, year, etc.

     

     

    Incoming = 
        // The measure adds the volumes for "Open" and "Closed" processes and subtracts the volume for "Open" processes from the previous day.
        // SUMX dim_calendar will do the daily calculation then sum it by other periods (month, quarter, year). 
        // You can also use MINX (lowest incoming that period), MAXX (highest incoming that period), or AVERAGEX (average incoming that period)
        SUMX(
			dim_calender,
			CALCULATE(
				SUM('Table'[Volume]),
				'Table'[Process] = "Open"
			) +
			CALCULATE(
				SUM('Table'[Volume]),
				'Table'[Process] = "Closed"
			)
			- CALCULATE(
				SUM('Table'[Volume]),
				'Table'[Process] = "Open",
				PREVIOUSDAY(dim_calender[Date])
			)
		)

     

     

    More information about SUMX: SUMX function (DAX) - DAX | Microsoft Learn

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from DataZoe.

     

    Hi OldDinosaur79 ,

     

    Did DataZoe's solution resolve your issue? Based on my tests, it seems to work correctly at each hierarchy level.

    Additionally, you can create another measure on top of the existing one:

    Measure  = SUMX(VALUES('dim_calender'[Date]),'Table'[YourMeasure])

    Let me know if you need any further assistance!

     

    Best Regards,
    Zhu

     

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