cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Avivek
Super User
Super User

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 RepExpected Return DateAct Return DateAct Return Date with Today's dateDaysCorrect ReturnLate Return% of late return
Ja29-09-2022 00:00 20-10-20222101 
Ja29-09-2022 00:0027-09-2022 00:0027-09-2022-210 
Ja01-10-2022 00:00 20-10-20221901 
Ja02-10-2022 00:00 20-10-20221801 
Ja02-10-2022 00:0026-09-2022 00:0026-09-2022-610 
Ja03-10-2022 00:00 20-10-20221701 
Ja05-10-2022 00:00 20-10-20221501 
Ja05-10-2022 00:0026-09-2022 00:0026-09-2022-910 
Ja07-10-2022 00:00 20-10-20221301 
    863667%

 

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:

  1. Act Return Date with Today's date = IF('Case Order Fact'[Returned Date]= BLANK(),TODAY(), 'Case Order Fact'[Returned Date])
  2. Days = DATEDIFF ('Case Order Fact'[Return Date],'Case Order Fact'[Late Return Date],DAY
  3. Late Return = IF( 'Case Order Fact'[Late Return Days]<=0,0,1)
  4. Correct Return = IF('Case Order Fact'[Late Return Days] <= 0, 1, 0 )
  5. % 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

 
 

Capture.PNG

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

Capture.PNG

c

 

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

 
 
 

 

 

 

 

1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

15 REPLIES 15
PaulDBrown
Super User
Super User

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] )
)

result.png





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown, it works only in few cases and doesn't work in most of the cases, one example is below 

 

I

Capture.PNG

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.

What is the code for the % Late returns measure?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown Its same what you had shared 

% Late Returns =
IF(
    ISINSCOPE('Case Order Fact'[CREATED_NAME]),
    BLANK(),
    DIVIDE([Late return],[Correct return]+[Late return])
)
 

Can you change the last two measures in the image to the SUMX measures and post the screenshot?
Also, which table is the Sales Rep field in the visual coming from and which field are you using in the SUMX measures?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






All the measures will be as below:

Correct Return Temp =
VAR _ReturnDate=
IF(
    ISBLANK(MAX('Case Order Fact'[Act Return Date])),
    TODAY(),
    MAX('Case Order Fact'[Act Return Date])
)
VAR _Diff=
DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)
RETURN
SWITCH(
    TRUE(),
    ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
    _Diff<0,1,
    0)
 
Correct return = SUMX('Case Order Fact',[Correct Return Temp])
 
Late Return Temp =
VAR _ReturnDate=
IF(
    ISBLANK(MAX('Case Order Fact'[Act Return Date])),
    TODAY(),
    MAX('Case Order Fact'[Act Return Date])
)
VAR _Diff=
    DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
        _Diff>0,1,
        0
    )
Late return =
SUMX('Case Detail Fact',[Late Return Temp])
 
% Late Returns =
IF(
    ISINSCOPE('Case Order Fact'[CREATED_NAME]),
    BLANK(),
    DIVIDE([Late return],[Correct return]+[Late return])
)

@PaulDBrown, i didn't follow, where are you asking me to use sumx.Are they for these measures:-

Late Return Temp =
VAR _ReturnDate=
IF(
    ISBLANK(MAX('Case Order Fact'[Act Return Date])),
    TODAY(),
    MAX('Case Order Fact'[Act Return Date])
)
VAR _Diff=
    DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
        _Diff>0,1,
        0
    )
 
 
Correct Return Temp =
VAR _ReturnDate=
IF(
    ISBLANK(MAX('Case Order Fact'[Act Return Date])),
    TODAY(),
    MAX('Case Order Fact'[Act Return Date])
)
VAR _Diff=
DATEDIFF(MAX('Case Order Fact'[Expected Return Date]),_ReturnDate,DAY)

RETURN
SWITCH(
    TRUE(),
    ISBLANK(MAX('Case Order Fact'[Expected Return Date])),BLANK(),
    _Diff<0,1,
    0
)
 
 
Also all the columns that I am using are from the same table, earlier i had used sales rep alone from another sales hierarchy dim but now i brought in the same fact from where returnand returned dates are used.

The measures you have posted are to then calculate the correct totals using SUMX:

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

and finally

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

If you have a Sales Rep Dimension table, you should be using it in the visual (it's more efficient). If so, you need to reference this dimension table in the SUMX measures.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown, this is exactly how we did, sales rep is the created name from the same table Case Order Fact, all the columns in measure are from same table. So am I missing something?

Why is it then not giving the right value?

Can you post a depiction of the visual with the Correct Return and Late return measures?
It would really help if you could provide a link to a sample PBIX file (you can change any confidential information). It's hard to see the problem otherwise. (since in my test it works...)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown, if i pull only those 2 columns and create the measures it seems to work fine but if i pulll all the columns in that view in a table and try the measure it doen't seem to work. I will share the test report from another account.

@PaulDBrown , this is the test report and here those measures does not seem to work. I am unable to undertand why it is so? Can you please help?

They do work. In your example, if you look at the detail of the rows in the dataset for the filtered sample, you will see why the SUMX returns a higher value.

The temp measure only looks at the dates in the filter context; the SUMX computes the date calculation row by row and then adds up the result of each row. There are two rows for each Expected Return Date and the Request ID field makes each of the rows unique. So there are 4 rows over which the SUMX calculation is carried out:

more rows.jpg

Edit to add: @Avivek 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown,I do not follow exactly what you are trying to say.

Certain places it shows me the correct value and certain places it shows differences in the values.

Capture.PNG

 

There are 2 issues to be seen here, firstly late return shows a different value from late return temp and on calculation this should be around 71%.

Firstly the late return is probably summing up the late return and secondly the late % value is also not showing the right value

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.