Calculate minimum date issue in Live Mode data connection

[Anonymous]

Hi Experts,

Is there a way I can achieve the below while having my datasource connection in Live mode?

The below code works when I use excel data source but it fails to recognise the Calculate function in live mode connection.

Expected Result = CALCULATE(MIN(DispenseTable[Date]),FILTER(DispenseTable,DispenseTable[PatientId]=EARLIER(DispenseTable[PatientId])&&DispenseTable[MP]=EARLIER(DispenseTable[MP])&&DispenseTable[Date]>EARLIER(DispenseTable[Date])))

Data

PatientId	Date	DrugID	MP (from DrugTable)	Expected Result
164798	6/01/2022 0:00	4802	Medicine 11	8/01/2022 0:00
164798	8/01/2022 0:00	4802	Medicine 11	10/01/2022 0:00
164798	10/01/2022 0:00	4664	Medicine 10	30/01/2022 0:00
164798	10/01/2022 0:00	4704	Medicine 10
164798	10/01/2022 0:00	4802	Medicine 11	12/01/2022 0:00
164798	12/01/2022 0:00	4802	Medicine 11	14/01/2022 0:00
164798	14/01/2022 0:00	4802	Medicine 11	18/01/2022 0:00
164798	18/01/2022 0:00	4802	Medicine 11	19/01/2022 0:00
164798	19/01/2022 0:00	4795	Medicine 11	21/01/2022 0:00
164798	19/01/2022 0:00	4802	Medicine 11
164798	21/01/2022 0:00	4802	Medicine 11	27/01/2022 0:00
164798	24/01/2022 0:00	-100	Non Coded Monitored Drug	21/02/2022 0:00
164798	27/01/2022 0:00	4802	Medicine 11	30/01/2022 0:00
164798	30/01/2022 0:00	4704	Medicine 10	7/02/2022 0:00
164798	30/01/2022 0:00	4794	Medicine 11	1/02/2022 0:00
164798	1/02/2022 0:00	4802	Medicine 11	7/02/2022 0:00
164798	5/02/2022 0:00	5411	Medicine 13	3/05/2022 0:00
164798	7/02/2022 0:00	4704	Medicine 10	2/03/2022 0:00
164798	7/02/2022 0:00	4802	Medicine 11	9/02/2022 0:00
164798	9/02/2022 0:00	4802	Medicine 11	10/02/2022 0:00
164798	10/02/2022 0:00	4802	Medicine 11	11/02/2022 0:00
164798	11/02/2022 0:00	4802	Medicine 11	13/02/2022 0:00

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Measure =
VAR Maxdate =
    CALCULATE (
        MAX ( DispenseTable[Date] ),
        FILTER (
            ALL ( DispenseTable ),
            DispenseTable[PatientId] = SELECTEDVALUE ( DispenseTable[PatientId] )
                && DispenseTable[MP] = SELECTEDVALUE ( DispenseTable[MP] )
                && [DrugID] = SELECTEDVALUE ( DispenseTable[DrugID] )
        )
    )
VAR Mindate =
    CALCULATE (
        MIN ( DispenseTable[Date] ),
        FILTER (
            ALL ( DispenseTable ),
            DispenseTable[PatientId] = SELECTEDVALUE ( DispenseTable[PatientId] )
                && DispenseTable[MP] = SELECTEDVALUE ( DispenseTable[MP] )
                && DispenseTable[Date] > SELECTEDVALUE ( DispenseTable[Date] )
                && [DrugID] = SELECTEDVALUE ( DispenseTable[DrugID] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( DispenseTable[Date] ) = Maxdate, BLANK (), Mindate )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

maybe you can try to create a measure

Measure = minx(FILTER(all('DispenseTable'),DispenseTable[PatientId]=max(DispenseTable[PatientId])&&DispenseTable[MP]=max(DispenseTable[MP])&&'DispenseTable'[Date]>max('DispenseTable'[Date])),'DispenseTable'[Date])

[Anonymous]

Hi @ryan_mayu 

Thanks heaps for providing me that code.

It works great for all of the rows except for the last row.

The last row for any medicine should be null when there are no further records for that medicine.

But I see some value picked for each medicine in the last row.

Appreciate your further help here.

[ryan_mayu]

@Anonymous 

pls try this

Measure = 
VAR _max=CALCULATE(max('DispenseTable'[Date]),ALLEXCEPT(DispenseTable,DispenseTable[PatientId],DispenseTable[MP]))
VAR _max2=CALCULATE(max('DispenseTable'[DrugID]),FILTER(ALL(DispenseTable),'DispenseTable'[PatientId]=max(DispenseTable[PatientId])&&DispenseTable[MP]=max(DispenseTable[MP])&&'DispenseTable'[Date]=_max),DispenseTable[DrugID])
return if(MAX('DispenseTable'[DrugID])=_max2&&MAX('DispenseTable'[Date])=_max,blank(),minx(FILTER(all('DispenseTable'),DispenseTable[PatientId]=max(DispenseTable[PatientId])&&DispenseTable[MP]=max(DispenseTable[MP])&&'DispenseTable'[Date]>max('DispenseTable'[Date])),'DispenseTable'[Date]))

pls see the attachment below