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	Act Return Date with Today's date	Days	Correct Return	Late Return	% of late return
Ja	29-09-2022 00:00		20-10-2022	21	0	1
Ja	29-09-2022 00:00	27-09-2022 00:00	27-09-2022	-2	1	0
Ja	01-10-2022 00:00		20-10-2022	19	0	1
Ja	02-10-2022 00:00		20-10-2022	18	0	1
Ja	02-10-2022 00:00	26-09-2022 00:00	26-09-2022	-6	1	0
Ja	03-10-2022 00:00		20-10-2022	17	0	1
Ja	05-10-2022 00:00		20-10-2022	15	0	1
Ja	05-10-2022 00:00	26-09-2022 00:00	26-09-2022	-9	1	0
Ja	07-10-2022 00:00		20-10-2022	13	0	1
				86	3	6	67%

Expected return date and Act return date are data's already available. In Act return date if it is blank it should take Today's date, difference of Expected Return Date and Act Return Date with Today's date is Days.

If Days is less than 0 then Correct Return is 1 else its 0, vice versa is for Late return where if Days is more than 0 then it is 1 else its 0.

% of late return calculation = Late Return/(Correct Return+Late Return), in above case that would be 6/(3+6)=0.667 and its percentage will be 67%.

Similarly I tried in power bi and these are the calculated columns created:

Act Return Date with Today's date = IF('Case Order Fact'[Returned Date]= BLANK(),TODAY(), 'Case Order Fact'[Returned Date])
Days = DATEDIFF ('Case Order Fact'[Return Date],'Case Order Fact'[Late Return Date],DAY
Late Return = IF( 'Case Order Fact'[Late Return Days]<=0,0,1)
Correct Return = IF('Case Order Fact'[Late Return Days] <= 0, 1, 0 )
% of Late Return = 'Case Order Fact'[Late Return Fl]/('Case Order Fact'[Late Return Fl]+'Case Order Fact'[Correct Return Fl])

In power bi it comes this way

It does not sum up the totals and if i try to sum up the total it doubles and shows as below:

It seems something very simple that i moght be missing, is there a way to see these values in power bi report simila

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 "

15 Replies

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?

Forum Discussion

Need help with the dax