check.pbix (131.9 KB)
Im trying to find the cumulative of Balance Current Month.
For Sep 2020, the Cumulative Balance should be 15.1M, and not 21.14M, and from there onwards cumulate.
Can you help.
Cumulative Balance =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR Result =
CALCULATE (
[Balance Current Month],
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MaxDate )
)
RETURN
Result
Create a relationship between Date and TB. Then update your measure to this:
Pro Tip:
-
If you don’t understand evaluation context very well then don’t opt for measure branching technique, I can see your measures go at least 4-5 levels deep, even a seasoned coder will have to use Pen and paper to note down and figure how everything works from inside out in your code.
-
Year column in Date table should be whole number and not a text field. Don’t use VALUE because this will slow down your code as this is a function which is not available with Storage Engine. The Bold rows in the below image shows that Storage engine has to call Formula Engine just to convert that text into whole number, this won’t happen if you change the format to whole number in the Power Query
.
TB Amount Total =
VAR CurrentDate = TODAY ()
VAR CurrentMonth = MONTH ( CurrentDate )
VAR CurrentYear = YEAR ( CurrentDate )
VAR DateFilters =
FILTER (
ALLSELECTED ( 'Date' ),
'Date'[MonthOfYear] = CurrentMonth
&& VALUE ( 'Date'[Year] ) = CurrentYear
)
VAR Result =
CALCULATE ( SUM ( TB[TB Amount] ), KEEPFILTERS ( DateFilters ) )
RETURN
Result
3 Likes
@Anu,
As usual, @AntrikshSharma is spot-on in his recommendations. One additional suggestion - change the relationship between Date and Collections to one way, active, one-to-many. The current 1:1 bidirectional relationship is likely to cause problems down the road.
Also, a couple of general things that will help you debug these sorts of problems:
- Organize your measures together in a measure table(s), rather than scattering them across the different fact and dimension tables.
Note: while I find this approach to be far superior to the scattered approach, some very expert Power BI users take the opposite perspective.
- Use standard accepted conventions to references to different types of elements in your DAX code:
Tables should be referenced as Table
Columns should be referenced as Table[Column]
Measures should be referenced as [Measure]
I noticed in your DAX that you are not always applying these conventions consistently, which will make it harder for you to debug your code, and MUCH harder for anyone else reading to understand it.
I hope this is helpful.
- Brian
1 Like
Yup, totally missed that point. Thanks!!!
Few doubts:
- why should I do this
TB Amount Total =
VAR CurrentDate = TODAY ()
VAR CurrentMonth = MONTH ( CurrentDate )
VAR CurrentYear = YEAR ( CurrentDate )
VAR DateFilters =
FILTER (
ALLSELECTED ( ‘Date’ ),
‘Date’[MonthOfYear] = CurrentMonth
&& VALUE ( ‘Date’[Year] ) = CurrentYear
)
VAR Result =
CALCULATE ( SUM ( TB[TB Amount] ), KEEPFILTERS ( DateFilters ) )
RETURN
Result
It is working fine with just SUM ( TB[TB Amount] , i just had to create relationship with Date table
- What is measure branching ? I din’t realize I am using it.lol.
Because it is DAX and not excel.
If it works for you then that’s fine, use it by all means, but then you are not leveraging full power of DAX. Look at the queries generated by the IF statement vs my version ( and that’s not even optimized yet ).
Your version (Look at the number of rows your code has to materialize ):
My version ( Still need to fix the VALUE issue and try many other combinations too ):
performance is really important when you work with DAX.
Branching is just nesting measures one inside other. Basically building measures on top of other measures.
I din’t understand this - Could you please explain?