Forum Discussion
Previous day value - SSAS Tabular live connection
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
Hi , Anonymous
Please refer to these related threads.
https://community.powerbi.com/t5/Desktop/Alternative-of-SELECTEDVALUE-DAX-function/m-p/812261
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.
3 Replies
- v-easonf-msft
Community Support
Hi , Anonymous
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]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.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
Hi , Anonymous
Please refer to these related threads.
https://community.powerbi.com/t5/Desktop/Alternative-of-SELECTEDVALUE-DAX-function/m-p/812261
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.