Previous day value - SSAS Tabular live connection

[manalla]

Hi,

I need to get previous day value and show it in column and then calculate difference between the values.

Below is my data set, which is an aggregated/summarized values from base table. I have many other dimension attributes on same table.

So main requirement is to pull previous day outstanding. First three fields are raw, last two are calcualted in below grid.

Power BI report connected to SSAS Tabular live connection.  I am restricted to use only measures and not calculated columns.

Formula's currently used :

Previous O/S =
CALCULATE(sum(Data[USDOutstanding]),ALLSELECTED(Data),PREVIOUSDAY(Data[Business_Date]))

PreviousDay_Blank = IF(ISBLANK(PREVIOUSDAY(Data[Business_Date])),1,0)

But since holiday/weekend days dates are not avilable on my data, previous day function is not working when it encounters missing date.

I have referred to this blog - https://community.powerbi.com/t5/Desktop/Previous-day-value/m-p/610754#M291094

but when i use EARLIER and use Business_DATE in it, it says Parameter is not correct type (cant find name Business_DATE). So not really sure what i should be using.

Any help will be really appreciated.

Business_Date	Commitment	Outstanding	Previous O/S	PreviousDay_Blank
5/18/2020	10000	9567		1
5/19/2020	10000	9000	9567	0
5/20/2020	10000	9500	9000	0
5/21/2020	10000	9600	9500	0
5/22/2020	10000	9750	9600	0
5/26/2020	10000	8500		0
5/27/2020	10000	8750	8500	0
5/28/2020	10500	10000	8750	0
5/29/2020	10500	9800	10000	0
6/1/2020	10500	10150		1
6/2/2020	10500	10150	10150	0
6/3/2020	10500	10150	10150	0
6/4/2020	10500	10220	10150	0
6/5/2020	10500	8670	10220	0
6/8/2020	11000	9000		1
6/9/2020	11000	9500	9000	0
6/10/2020	11000	9650	9500	0
6/11/2020	11000	9650	9650	0
6/12/2020	11000	9650	9650	0
6/15/2020	11000	9650		1

Thanks

Manoj

[v-easonf-msft]

Hi , @manalla 

You may check if the following formulas help:

Previous O/S measure = 
CALCULATE(
    SUM(Test[Outstanding]),
    FILTER(
        ALL(Test),
        Test[Business_Date]=SELECTEDVALUE(Test[Business_Date])-1
    )
)

flag = 
IF(
    ISBLANK([Previous O/S measure]),
    1,0
)

Result = SELECTEDVALUE(Test[Outstanding])-[Previous O/S measure]

 sample file

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manalla]

Hi Eason @v-easonf-msft 

Thanks a lot for your reply.

I am using SSAS Live connection. So SELECTEDVALUE function is not working. Even EARLIER function is givnig errors.

I get "SELECTEDVALUE" is not a function.

I referred to this Post - https://community.powerbi.com/t5/Desktop/SELECTEDVALUE-not-a-valid-function/td-p/270639 which is an old post. 

Can you suggest any other alternate for my case?

Thanks

Manoj

[v-easonf-msft · Accepted solution]

Hi , @manalla 

Please refer to these related threads.

https://community.powerbi.com/t5/Desktop/Alternative-of-SELECTEDVALUE-DAX-function/m-p/812261

https://community.powerbi.com/t5/DAX-Commands-and-Tips/SELECTEDVALUE-function-doesn-t-recognized/m-p...

You can  take a try to use IF + HASONEVALUE combination  to replace the" selectvalue" function in DirectQuery mode.

IF（HASONEVALUE（Table [column]），VALUES（Table [column]））

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.