Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a calculation of accounts receivables
Receivables =
VAR MaxDate = MAX ( 'Date'[Date] )
VAR NowDate = Today()
RETURN
CALCULATE (
'Receivables'[Receivables Amount Posted],
'Date'[Date] <= MaxDate && 'Date'[Date] <= NowDate,
Removefilters ( 'Date' )
Which works just fine, but once the customer has paid then the receivables is zero (also correct), but I would like to get those removed from my visual.
For example invoice "A" is issued in Jan 23 and paid in Mar23, so it will look like this:
Month | Invoice | Receivables |
jan-23 | A | 100 |
feb-23 | A | 100 |
mar-23 | A | 0 |
apr-23 | A | 0 |
maj-23 | A | 0 |
jun-23 | A | 0 |
jul-23 | A | 0 |
aug-23 | A | 0 |
sep-23 | A | 0 |
okt-23 | A | 0 |
nov-23 | A | 0 |
dec-23 | A | 0 |
I would like it to be blank when zero. I have tried an if
Try
Receivables =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR NowDate =
TODAY ()
VAR Result =
CALCULATE (
'Receivables'[Receivables Amount Posted],
'Date'[Date] <= MaxDate
&& 'Date'[Date] <= NowDate,
REMOVEFILTERS ( 'Date' )
)
RETURN
IF ( Result <> 0, Result )
Thanks for the feedback - I think it is close. It doesn't change that I still get zeros included, but if I instead of
IF ( Result <> 0, Result )
Change it to
IF ( OR(Result < -1,Result >1), Result )
Then I don't get all the zeros anymore (but obviously not anything between -1 and 1 either), so my change is not a solution, but I can't grasp why the measure you propose does work fine, but just not when it should be different to zero. If I use different that 0.99 it also doesn't work, it has to be 1
My guess would be that it is not returning exactly 0 but instead it rounds to 0. Use Performance Analyzer to get the DAX code for the table or matrix visual and run it in DAX Studio.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |