Direct query - Previous month variance

[PBILearner2022]

Hi Team,

I am currently using Direct Query i.e SAP HANA as source , some built in functions are no longer working.

I need a measure to calculate previous month variance. These measure should calculate the variance from the latest data avaialbe in the table.

I dont have a date column and I am trying previousmonth whihcis not working is there any way we can get the variance in direct query in power bi without date table created?

I am struck with this issue please help

Sample Data :

score    Run_date
52   02/02/2022
63   02/02/2022
44   03/02/2022
55   02/02/2022

Here are my two measures for Current Month and Prior Month PM:
Current Month Score = Average(score )

Current Month MOM Variance =

Var Currentmonth= [Current Month Score]

Var PeviousMonths =CALCULATE([Current Month Score],PREVIOUSMONTH(Trend[DATE]))

var Difference =Currentmonth-PeviousMonthDQ

return

If (PeviousMonthDQ <>BLANK(),Difference,0)


expected O/p:

Table format where It should show only the current month score and variance difference between current month and previous month 
Score   Variance

57.5      8.3

[PBILearner2022]

@amitchandak Thank you for your reply. As per your suggestion i have created a date table and used the above formula but it is returning blank.
I have marked my table as date table.

test = CALCULATE(avg(com_cal),PREVIOUSMONTH(CurentData[RUN_DATE]))

Please help

[v-chenwuz-msft]

Hi @PBILearner2022 ,

If your model has dim date table. please make sure relationship between date table and fact table is one to many and cross filter direction is single. Then use 'Dim Date' table as the first parameter of PREVIOUSMONTH function. The same as PREVIOUSMONTH('Dim Date'[RUN_DATE]).

If your model there is no dim date table, please add some funtion like ALL(),REMOVEFILTER()... to remove date filter on each row context. Please refer the following code:

test = CALCULATE(AVERAGE('Table'[score]),all(),PREVIOUSMONTH('Table'[Run_date]))

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[PBILearner2022]

Thank you @v-chenwuz-msft by using the above measure I am getting the previous month variance , but in my scenario i will be having last 6 months data in that particular data set and I need to display the latest month data and previous month variance alone in a table .
Is there any way I can create flag it as based on max of date in direct query mode in DAX and use it as filter ?

[amitchandak]

@PBILearner2022 , For this you need a date table

Var PeviousMonths =CALCULATE([Current Month Score],PREVIOUSMONTH(Date[DATE]))

You said that - I dont have a date column  - what do you have to get last month ?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.