DAX day query across import and direct query tables

[DataSkills]

Hi there, 

I have a project where I am unable to use Power BI premium to create a hybrid model, and I need to refresh my data close to real time so cannot do scheduled data refreshes. 

The setup is that I have an import table (data older than today) and structurally identical direct query table for data relevant to today.  Let's call them 

• Widgets_History
• Widgets_Today

The tables have a process date field (date/time), and a widget count field (integer field) which counts how many widgets are produced per production line. 

I am trying to work out how I might be able to create a measure that works out a % comparison of widgets produced per day of the week against average for preceeding 3 months. 

So if I pick a Monday, the measure would need to do as follows:

• Is today Monday?
  • Yes - get total widgets produced today from the direct query table.
  • No - get total widgets produced on most recent Monday from Import table. 
• Work out average widgets for preceding Mondays for last 3 months (not sure if we should INCLUDE or EXCLUDE the latest Monday so happy to accept either way at the moment!)
• Work out latest Monday's production as a percentage of the average for the preceding 3 months' Monday's productions!

Example - we produced 154 widgets this Monday. Avg for the preceding 3 months is 130. 154/130 = 118%

I don't even know how to start! Any pointers would be gratefully received! Thank you. 

[DataSkills]

Hi @v-yinliw-msft wow what an amazing effort you put into answering this! So grateful! I think this is working but still checking a few things before I give final input and accept as solution. Either way, I am blown away by the time you put into this answer. 🌟

[v-yinliw-msft]

Hi @DataSkills ,

Has your problem been solved?

If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out and let me know.

Best Regards,

Community Support Team _Yinliw

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

[v-yinliw-msft · Accepted solution]

Hi @DataSkills,  

You can try this method:

I don’t know what your data look like, so I create a sample data table.

Widgets_Today table:

Widgets_History table:

Then I create a date table to be a slicer to fit the sample data:

New table:DateTable = CALENDAR(DATE(2022,10,1), DATE(2022,11,3)) 

Then I create columns to show the weekday of the date.

Weekday = WEEKDAY('Widgets_History'[Date],2)

Weekday = WEEKDAY('DateTable'[Date],2)

Because of the Widgets_Today table is DQ connection, so need to create a measure:

MWeekday = WEEKDAY(ALL(Widgets_Today[Date]),2)

Then I create measures:

New measures:

TodayORRecentTotal = VAR _DQ = SUM ( Widgets_Today[Widget Count] ) VAR _Import = CALCULATE ( SUM ( Widgets_History[Widget Count] ), FILTER ( 'Widgets_History', 'Widgets_History'[Weekday] = SELECTEDVALUE ( DateTable[Weekday] ) ), KEEPFILTERS ( FILTER ( 'Widgets_History', MAX ( 'Widgets_History'[Date] ) ) ) ) VAR _Day = DAY ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _CurrentMonth = MONTH ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _Month = IF ( _CurrentMonth >= 4, _CurrentMonth - 3, _CurrentMonth + 9 ) VAR _CurrentYear = YEAR ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _Year = IF ( _CurrentMonth >= 4, _CurrentYear, _CurrentYear - 1 ) VAR _PastThreeMonths = CALCULATE ( SUM ( Widgets_History[Widget Count] ), FILTER ( 'Widgets_History', 'Widgets_History'[Weekday] = SELECTEDVALUE ( DateTable[Weekday] ) && 'Widgets_History'[Date] >= DATE ( _Year, _Month, _Day ) ) ) VAR _CountRows = CALCULATE ( COUNTROWS ( 'Widgets_History' ), FILTER ( 'Widgets_History', SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_History'[Weekday] ) ) VAR _Total = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], _PastThreeMonths + _DQ, _PastThreeMonths ) VAR _AveragePastThreeMonths = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], DIVIDE ( _Total, _CountRows + 1 ), DIVIDE ( _Total, _CountRows ) ) VAR _TodayTotal = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = WEEKDAY ( TODAY (), 2 ), _DQ, _Import ) VAR _Percentage = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], DIVIDE ( _DQ, _Total ), DIVIDE ( _TodayTotal, _Total ) ) RETURN _TodayTotal 

Average = VAR _DQ = SUM ( Widgets_Today[Widget Count] ) VAR _Import = CALCULATE ( SUM ( Widgets_History[Widget Count] ), FILTER ( 'Widgets_History', 'Widgets_History'[Weekday] = SELECTEDVALUE ( DateTable[Weekday] ) ), KEEPFILTERS ( FILTER ( 'Widgets_History', MAX ( 'Widgets_History'[Date] ) ) ) ) VAR _Day = DAY ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _CurrentMonth = MONTH ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _Month = IF ( _CurrentMonth >= 4, _CurrentMonth - 3, _CurrentMonth + 9 ) VAR _CurrentYear = YEAR ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _Year = IF ( _CurrentMonth >= 4, _CurrentYear, _CurrentYear - 1 ) VAR _PastThreeMonths = CALCULATE ( SUM ( Widgets_History[Widget Count] ), FILTER ( 'Widgets_History', 'Widgets_History'[Weekday] = SELECTEDVALUE ( DateTable[Weekday] ) && 'Widgets_History'[Date] >= DATE ( _Year, _Month, _Day ) ) ) VAR _CountRows = CALCULATE ( COUNTROWS ( 'Widgets_History' ), FILTER ( 'Widgets_History', SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_History'[Weekday] ) ) VAR _Total = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], _PastThreeMonths + _DQ, _PastThreeMonths ) VAR _AveragePastThreeMonths = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], DIVIDE ( _Total, _CountRows + 1 ), DIVIDE ( _Total, _CountRows ) ) VAR _TodayTotal = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = WEEKDAY ( TODAY (), 2 ), _DQ, _Import ) VAR _Percentage = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], DIVIDE ( _DQ, _Total ), DIVIDE ( _TodayTotal, _Total ) ) RETURN _AveragePastThreeMonths 

Percentage = VAR _DQ = SUM ( Widgets_Today[Widget Count] ) VAR _Import = CALCULATE ( SUM ( Widgets_History[Widget Count] ), FILTER ( 'Widgets_History', 'Widgets_History'[Weekday] = SELECTEDVALUE ( DateTable[Weekday] ) ), KEEPFILTERS ( FILTER ( 'Widgets_History', MAX ( 'Widgets_History'[Date] ) ) ) ) VAR _Day = DAY ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _CurrentMonth = MONTH ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _Month = IF ( _CurrentMonth >= 4, _CurrentMonth - 3, _CurrentMonth + 9 ) VAR _CurrentYear = YEAR ( SELECTEDVALUE ( DateTable[Date] ) ) VAR _Year = IF ( _CurrentMonth >= 4, _CurrentYear, _CurrentYear - 1 ) VAR _PastThreeMonths = CALCULATE ( SUM ( Widgets_History[Widget Count] ), FILTER ( 'Widgets_History', 'Widgets_History'[Weekday] = SELECTEDVALUE ( DateTable[Weekday] ) && 'Widgets_History'[Date] >= DATE ( _Year, _Month, _Day ) ) ) VAR _CountRows = CALCULATE ( COUNTROWS ( 'Widgets_History' ), FILTER ( 'Widgets_History', SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_History'[Weekday] ) ) VAR _Total = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], _PastThreeMonths + _DQ, _PastThreeMonths ) VAR _AveragePastThreeMonths = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], DIVIDE ( _Total, _CountRows + 1 ), DIVIDE ( _Total, _CountRows ) ) VAR _TodayTotal = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = WEEKDAY ( TODAY (), 2 ), _DQ, _Import ) VAR _Percentage = IF ( SELECTEDVALUE ( DateTable[Weekday] ) = 'Widgets_Today'[MWeekday], DIVIDE ( _DQ, _Total ), DIVIDE ( _TodayTotal, _Total ) ) RETURN _Percentage 

As for the Percentage, I change the type of the result here to show it by percent.

 

These three measures' var are all same, only the return value has a little different based on the requests.

The result is:

 

And here is my PBIX file, in the table Widgets_Today ,measure Average, I wrote comments for all the variables to help you understand the code.

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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