Time Intelligence - Data Table Relationship with CALENDAR date not working

[AlanBaker]

Hi

I have what appears to be the same issue previously reported , I have Created a Calendar Table with a Unique ID which I am using to perform Date Intelligence in order to determine planned costs for the previous week and comparing the result to the current week. You will see two tables below the first selects the fields I require BUT uses the Calendar Table "Date" field this appears to be working AOK.

The SECOND Table mirrors the first table EXCEPT that I have removed the Calendar "Date" field and substituted the "Week Begining" field from my data table and as you can see the dates between the two tables do NOT appear to be linking as the reults for the previous week are NOT populated?

I have read the helpful advice in the replies to similar problems and I believe that the imported table "Week Begining" field is formatted as Date d/mm/yyyy within the PBI Query Editor. 

  Query Editor The Data View of the Calendar table creation

The Calendar table which is created within the PBI Data View is formatted again as Date. I have Applied the Queries and Refreshed the graphics and data tables (several times 🙂

The Calendar Table is Linked as a ONE from the "Date" Column to the MANY column 'WeekBegining" field in the data table.

Help to solve this would be greatly appreciated....

[AlanBaker]

I have what appears to be the same issue previously reported , I have Created a Calendar Table with a Unique ID which I am using to perform Date Intelligence in order to determine planned costs for the previous week and comparing the result to the current week. You will see two tables below the first selects the fields I require BUT uses the Calendar Table "Date" field this appears to be working AOK.

The SECOND Table mirrors the first table EXCEPT that I have removed the Calendar "Date" field and substituted the "Week Begining" field from my data table and as you can see the dates between the two tables do NOT appear to be linking as the reults for the previous week are NOT populated?

I have read the helpful advice in the replies to similar problems and I believe that the imported table "Week Begining" field is formatted as Date d/mm/yyyy within the PBI Query Editor. 

  Query Editor The Data View of the Calendar table creation

The Calendar table which is created within the PBI Data View is formatted again as Date. I have Applied the Queries and Refreshed the graphics and data tables (several times 🙂

The Calendar Table is Linked as a ONE from the "Date" Column to the MANY column 'WeekBegining" field in the data table.

Help to solve this would be greatly appreciated....

[TomMartens · Accepted solution]

Hey AB,

each measure is evaluated in the so called Filter Context. The Filter Context filters down the table used in the expression, in your example this is the table KPI2_... This means before the expression is evaluated the current Current Filter context is applied to the table. The Filter Context is determined by the selections made from the slicers and also by the row and column headers. After the Filter Context is applied the expression is evaluated to the remaining rows, this explains why we always (at least most of the time have to aggregate functions like SUM('KPI2_...'[PlannedCosts]). This becomes different if we use iterator funtions like SUMX() and the lot. To me it seems, that most measures I calculate require that this Filter Context has to be adjusted. This adjustment is possible if we us CALCULATE(<expression>, FITLER1,...FILTERn).

If you use the Date column from your Calendar table something called Filter Propagation happens. The current selection from the Calendar table is propagated to the many side of an existing relationship.

The working linkage of your Calendar table and your fact table 'KPI2_...' is demonstrated by the 1st table of your screenshot.

If you use the Date column from the table 'KPI2_...' this selection is used to filter this table and the [PlannedCosts] column in your first post shows the correct value (the 2nd table in your screenshot).

If you use the Date column from the table 'KPI2_..." in your table visual, there is no filter on the Calendar table for this reason the FILTER(...) will always return the same table containing the last 7 seven days of your Calendar table. This table is then used to Filter down your 'KPI2_' table. I assume that there are no values available in your table 'KPI2_...' for these last 7 days, for this reason no value is shown for your measure LASTWEEK_PLAN_COSTS 

You may reconsider using the Calendar table for your visuals from my experience this is a common practice, if you won't or can't then you have to rewrite your measure to use your Date column from your table 'KPI2_...'.

In addition to Matt Allington's book I also recommend the following books from Marco Russo and Alberto Ferrari

• Analyzing Data with Microsoft Power BI and Power Pivot for Excel
• The Definitive Guide to DAX

Regards

[TomMartens]

Hey,

I'm wondering what your issue (the thing you want to solve exactly is).

As I understand so far

• you use a separate date-table (this will always be my recommendation)
• if you use the date column from the calendar table everything works fine?!?!
• if you use the date column from your fact table (manufacture), you don't see what you expect

Please excuse my question,why do you want to use the date column from your facttable?

Maybe you have to adjust the calculation for "LASTWEEK_PLAN_COSTS", if this formula references the date column from your calendar table, but it's not used, maybe this can explain why the measure does not show any values.

If you just want to change the name from Date to WeekBeginning just mark the Date column in the table visual and select "Rename" from the context menu of the column.

From my point of view, it's always a good idea to hide the fields from the fact tables, that are related to dimension tables (lookup tables), this prevents issues by choosing the "wrong" column. And just work with the columns from dimension tables for slicer selection or as column for visuals like the table visual.

Regards 

[AlanBaker]

Hi Tom

Many thanks for the prompt response and your question is gratefully received....

The Calendar table was built by me to allow the full use of the Time Intelligence ability in DAX  as described in the Book "Learn to Write DAX" by Matt Allington. The purpose of this Calendar table is to support very flexible Date Arithmetic for things like dynamically calculating the difference from last week to this week using the Calendar Unique ID  functionality (see Cal_ID in example measure beow). I don't believe that the Calendar table is intended to form an integral part of Power BI graphical solutions & reporting other than to act on the data as part of calculated measures as per this live example:

LASTWEEK_PLAN_COSTS = CALCULATE(
      SUM(KPI2_RM_SC_WOBacklog_History_Stats[PlannedCosts]),
      FILTER(
       ALL('CALENDAR'),
       'CALENDAR'[Cal_ID] >= MAX('CALENDAR'[Cal_ID]) - 7 &&
       'CALENDAR'[Cal_ID] < MAX('CALENDAR'[Cal_ID])
      )
     ) 

The problem is:

I want to be able to use the date field in my fact table.

 I have everything right (I hope) except the linkage between the fields which appears not to work as explained and this is worrying as it builds an element of "unknown" into my Dashboards that MIGHT hide other as yet unknown issues?

There are a few other posts about this same issue which I have read and acted on but without  a resolution yet...

Regards

AB

[TomMartens · Accepted solution]

Hey AB,

each measure is evaluated in the so called Filter Context. The Filter Context filters down the table used in the expression, in your example this is the table KPI2_... This means before the expression is evaluated the current Current Filter context is applied to the table. The Filter Context is determined by the selections made from the slicers and also by the row and column headers. After the Filter Context is applied the expression is evaluated to the remaining rows, this explains why we always (at least most of the time have to aggregate functions like SUM('KPI2_...'[PlannedCosts]). This becomes different if we use iterator funtions like SUMX() and the lot. To me it seems, that most measures I calculate require that this Filter Context has to be adjusted. This adjustment is possible if we us CALCULATE(<expression>, FITLER1,...FILTERn).

If you use the Date column from your Calendar table something called Filter Propagation happens. The current selection from the Calendar table is propagated to the many side of an existing relationship.

The working linkage of your Calendar table and your fact table 'KPI2_...' is demonstrated by the 1st table of your screenshot.

If you use the Date column from the table 'KPI2_...' this selection is used to filter this table and the [PlannedCosts] column in your first post shows the correct value (the 2nd table in your screenshot).

If you use the Date column from the table 'KPI2_..." in your table visual, there is no filter on the Calendar table for this reason the FILTER(...) will always return the same table containing the last 7 seven days of your Calendar table. This table is then used to Filter down your 'KPI2_' table. I assume that there are no values available in your table 'KPI2_...' for these last 7 days, for this reason no value is shown for your measure LASTWEEK_PLAN_COSTS 

You may reconsider using the Calendar table for your visuals from my experience this is a common practice, if you won't or can't then you have to rewrite your measure to use your Date column from your table 'KPI2_...'.

In addition to Matt Allington's book I also recommend the following books from Marco Russo and Alberto Ferrari

• Analyzing Data with Microsoft Power BI and Power Pivot for Excel
• The Definitive Guide to DAX

Regards

[AlanBaker · Accepted solution]

Thanks Tom, you make perfect sense and thankyou for taking the time to make the detailed explanation 🙂 (BTW I have also purchased a copy of Marco & Alberto's "The Definitive Guide to DAX"

Regards

AB

[TomMartens]

Your welcome, it has been a pleasure.