Passing date measure to another measure

[Anonymous]

I am about to lose my patience with an issue I'm facing, that I simply cannot comprehend why it's happening. Of course I'm missing something, so I was hoping the friednly community here could help me out:

I have a date measure that calculates a relative date based on a slicer selection -- specifically, it takes the slicer value and calculates the date one year before the date specified in the slicer selection as follows:

minus12Months.date = date(YEAR(CALCULATE(MAX(Main_Measures[DateFeed].[Date]),filter(Main_Measures,[Total Revenue]<>0)))-1,month(CALCULATE(MAX(Main_Measures[DateFeed].[Date]),filter(Main_Measures,[Total Revenue]<>0))),1)

where:

Main_Measures[DateFeed].[Date] is a calendar table that is being used across the report in date calculations

[Total Revenue] is a measure that simply sums revenue values from a bunch of data tables

For our example, let's assume that the above measure calculates the correct date I want, in this case June 1, 2017.

This measure I have created gives me exactly what I want -- a simple date value (and it's specified as a date type).

The issue is when I try to use this measure in another measure, I don't get an error but I still don't get any result (i get "Blank"). When I hardcode the date value I want in this second measure, the calculation works as it should.

This is the second measure:

minus12Months.Revenue = CALCULATE([Total Revenue],filter(Main_Measures,Main_Measures[DateFeed].[Date]=[minus12Months.date]))

which doesn't work.

If, instead, I hardcode the date value (which is the date value my first measure above calculates correctly), then the calculation works:

minus12Months.Revenue = CALCULATE([Total Revenue],filter(Main_Measures,Main_Measures[DateFeed].[Date]=date(2017,6,1)))

I have also tried taking the first measure and extracting year, month, and day so I can use them in a date function in my second measure (= date(year(minus12Months.date),month(minus12Months.date),day(minus12Months.date)) ) with no luck.

My understanding is that both my first measure (minus12Months.date) AND the date() function both return actual date object types, so in theory it should work. But it doesn't....

Any suggestions are greatly appreciated!

[Anonymous]

Mate, first of all, you should never, ever, ever rely on the automatically generated date table in Power BI. NEVER. Easy as that.

Instead, create a proper Date table ("proper" does actually mean something very important here - you must designate the table as DATE TABLE in Power BI) and create a relationship between the table and your fact table. Then, and only then, will you be able to author simple DAX measures that will use time intelligence.

FORGET ABOUT THE AUTOMATIC DATE TABLES ONCE AND FOR ALL. Never, ever, ever use the notation Table[Date].[Date]. EVER. There is too much to be said about it here. If you want to know more, please read some good books on DAX.

If you have a proper Date table, then you can use, for example, SAMEPERIODLASTYEAR( Dates[Date] ) to move back in time exactly to the same period you've selected in your Dates table but in the previous year. There are many other functions that will work correctly only if you have a PROPER DATE TABLE.

Best

Darek

[Anonymous]

Thanks for clarifying and for the tip -- I believe I have already set up my model in the way you suggested. See below for the relationships:

The DateTable is a table that I generated using the CALENDAR() function, and using start and end dates as specified by firstdate() and lastdate() respectively, from tables in my input model. So, is this DateTable considered a "proper" date table? I've designated it as a DATE TABLE.

Moving on, this table feeds the dates into the Main_Measures table via the relationship I have created. The Main_Measures table holds a couple of calculated columns that all use the DateTable as input, along with a bunch of measures.

I tried using the SAMEPERIODLASTYEAR() function, however I'm not getting the correct calculation (I'm getting a sum of the entire last year values instead of the single month's values, just from one year ago.

Hopefully this provides some clarity. Any idea how to fetch the individual's month value from last year?

[Anonymous]

One more thing again... Why is the connection between the Date table and your fact table 1-to-1? What fields do you join on?

Best

Darek

[Anonymous]

Some more things...

A proper date table covers all FULL years that can be found in any of your date fields in any of the tables. FULL YEARS means from the first to the last day of each individual year. If you don't have full years, you can't use the time intelligence functions.

Best

Darek

[Anonymous]

@Anonymous  thank you so much for the elaborate responses.

Here is the full model overview. The DateTable (which takes the FULL year range from my tables at the bottom and produces a Date table) has a 1:1 relationship with the Main_Measures[DateFeed] table, which also holds all my measures as mentioned previously.

My end goal is this:

For a selected date in my slicer, I want to be able to calculate and show the Last Twelve Months (or Trailing Twelve Months) revenue growth. So, in my mind I have to first find the max date in my current selection from the slicer and get the corresponding revenue figure, then fetch the revenue figure from 12 months ago, and do the growth calculation. Sounds simple, but I'm lost somewhere.

Example:

My slicer has FY19 selected. Assuming that FY19 has not yet ended and we are currently in month 9 of FY19, I want to fetch the revenue value of month 9 of FY18.

I have successfully created a measure that holds the "date-from-twelve-months-ago". It's just that for some reason I cannot use this measure as a filter in a CALCULATE() function so I can get the actual revenue figure from twelve-months-ago.

[Anonymous]

One more thing...

Your Main_Measures table should not link to your fact tables. If you really need it (I can't see a reason for it), then it should stand on its own because I suspect it's linking to your facts via the date field... It's DateTable that should link to them.

Best

Darek

[Anonymous]

OK.

1. Looks like DateFeed is always the first date of a month - is this correct? If not, please tell me what dates you're storing in there. I'm particularly curious about the 1-1 relationship between DateTable and your fact table. But I've spotted something strange... Why is the DateTable not linking directly to you fact tables? Why are you going through the Main_Measures table? This does not look right. Please connect DateTable to your facts on the Date field.

2. Your slicer values must come from DateTable, nothing else. If I were you, I'd hide all the date fields in your fact tables or I'd downright delete them (apart from the field that DateTable should link to). You'll thank me later 🙂

3. Your DateTable should have all the necessary date attributes for your analysis.

4. Once you've done the above, please post your model again.

Best

Darek

[Anonymous]

OK. Again, you should only use single direction filters in your models as much as possible. There are certain circumstances where cross-filtering should be enabled but these are very specific ones and most models should not enable them by default. Please turn off in Power BI the feature which defaults filters to both-way (if you have not yet done it). If you don't know exactly how both-way filtering works, then you should not use it. And trust me, you have to read a bit upon this topic before you can SAFELY use them. There's too much to be said about it here. Again, I should ask to read upon this topic. One of the best websites about DAX, if not the best in the world, is www.sqlbi.com.

Secondly, please take a snapshot of all your model (not just part of it) and paste so that I can see it in full glory 🙂

Which field is the DateTable connected to?

Tip: Fact table should not store anything apart from figures (the measurements of your process that the table models). All other things should be attributes of dimensions that should join to the fact table through keys (best: integers). Ideally, between your dimensions and the fact table only 1-to-many relationships should exist.

Best

Darek