Forum Discussion

Post Partisan

3 years ago

Solved

Need help with the dax

Hello everyone! Recently I have created a report based on an excel which has few calculations. The calculations in the excel are as below: Sales Rep Expected Return Date Act Return Date Ac...

Need Help

Show and Tell

Tips and Tricks

PaulDBrown
3 years ago
Add the Request ID field to the visual and you will see why the values are "different "

PaulDBrown

Community Champion

3 years ago

Try with the following measures:

CORRECT Return Temp =
VAR _ReturnDate =
    IF (
        ISBLANK ( MAX ( fTable[Act Return Date] ) ),
        TODAY (),
        MAX ( fTable[Act Return Date] )
    )
VAR _Diff =
    DATEDIFF ( MAX ( fTable[Expected Return Date] ), _ReturnDate, DAY )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( MAX ( fTable[Expected Return Date] ) ), BLANK (),
        _Diff < 0, 1,
        0
    )

Correct return = 
SUMX(fTable, [CORRECT Return Temp])

LATE Return Temp =
VAR _ReturnDate =
    IF (
        ISBLANK ( MAX ( fTable[Act Return Date] ) ),
        TODAY (),
        MAX ( fTable[Act Return Date] )
    )
VAR _Diff =
    DATEDIFF ( MAX ( fTable[Expected Return Date] ), _ReturnDate, DAY )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( MAX ( fTable[Expected Return Date] ) ), BLANK (),
        _Diff > 0, 1,
        0
    )

Late return = 
SUMX(fTable, [LATE Return Temp])

% Late returns =
IF (
    ISINSCOPE ( fTable[Sales Rep] ),
    BLANK (),
    DIVIDE ( [Late return], [Correct return] + [Late return] )
)

Avivek
Post Partisan
3 years ago
PaulDBrown, it works only in few cases and doesn't work in most of the cases, one example is below

I
It should ne showing 60% but it shows 35.71%, can you suggest something else because i am unable to understand what is the error.
- PaulDBrown
  Community Champion
  3 years ago
  What is the code for the % Late returns measure?
  - Avivek
    Post Partisan
    3 years ago
    PaulDBrown Its same what you had shared
    % Late Returns =
    IF(
    ISINSCOPE('Case Order Fact'[CREATED_NAME]),
    BLANK(),
    DIVIDE([Late return],[Correct return]+[Late return])
    )