Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

not getting correct output using if condition

I am tring to get max date according to date field -my mai objective is to get the closest date data to filter selected date.

to achive this i created a measure-1 which gets selected date and using that masure i found the date which are smaller to the selected date with measure-2 and then i get date to the fields which is having measure-2 value =1. in measure three and now i want max date from the date in measure 3 but as its a measure i can not use max function on it .

and i have tried setting coloumn but it gives inappropriate out put.

Measure 2 = SELECTEDVALUE('Date'[Date],TODAY())
Measure 4 = if([Measure 2]>=MIN(hello[Reporting Date]),1,0)
Measure 5 = IF([Measure 4]=1,CALCULATE(MAX(hello[try]),ALLSELECTED(hello[Loan Number])),0)
this is to get max value but it give overall max value.
Column = IF([Measure 4]=1,MAX(hello[asd]),0)
asd = IF([Measure 5]=hello[try],MAXA(hello[Reporting Date]),0)
try is just the copy of reporting date column.
i want row which are having 43931.

its the report

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Honestly... can't make heads or tails out of all of this...

    Would you mind stating the problem in simpler terms and maybe adding some text data, or well thought out pictures?

    Best
    D

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I want to get data according to filtered date like i have filtered date 15 apr 2020 then if the eaxct date not present reporting date then the  closest date before to filtered data to be fetched. mostly the data we are getting on 10th or 5th of month.

     

    like you can see image i have selected 15th in filter which i am getting through measure 2

    Measure 2 = SELECTEDVALUE('Date'[Date],TODAY())
    then i am getting date that are below or equal to filtered date in measure 4
    Measure 4 = if([Measure 2]>=MIN(hello[Reporting Date]),1,0) 
    if  its below or equal then its sets 1 and if not then 0
    in measure 5 i am tring to get date istead of 1 which is become number due datatype problem bt its ok.
    Measure 5 = IF([Measure 4]=1,CALCULATE(MAX(hello[try]),ALLSELECTED(hello[Loan Number])),0)
    now if i want to get the max value from measure 5 i have to get it in column filed because max works on only coloumn
    so column asds
     
    asd = IF( ([Measure 5]<>0),hello[Reporting Date],0)
    but its getting different out put as you can see in image.
    column named column i am fetching max asd value which is wrong because asd is calculating wrong
    Column = IF([Measure 4]=1,MAX(hello[asd]),0)
     i want only those field which are having 43931 which is cloesest date to the 15 apr 2020.  you can see in reporting date.
    try date is same copy of reporting date i used it to try only its not making any diffrence