Comparing this year and last year measure on a bar chart

[AliSharifi]

Hi,

I'm going to show a meaure values of this year and last year on a bar chart. I created a new measure in PowerBI Desktop with this formula:

The problem is when I use a Time Slicer and select a year, The calculated measure be disapeared and It sounds the last year data getting filter out from the dataset:

I can see the OrderQuantityLY just when more than 1 year is selected:

Any suggestion?

Thanks,

Ali

[Sean · Accepted solution]

@AliSharifi Create a clustered column chart

In the Axis - enter CalendarMonth (this should be a field from the DimDate table that represents just the months)

In the Legend - enter CalendarYear (again from the DimDate table the field that represents just the years)

this field CalendarYear should be the same field you use for the Slicer

In the Value - enter this measure OrderQuantity = SUM(FactInternetSales[OrderQuantity])

This will create a chart that will show 12 columns - overall totals for Jan thru Dec for all years

Then select as many years in the slicer as you wish - you will get that many columns for each month

this works with line charts too and you can select any years to compare not just consecutive

[Isra_BI]

I've already created a Calendat Table with the following setup:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ))

So, which are the following steps now? I've got to create a relationship between column "Date" (calendar Table) and the column "CreationDate"?

Which column do I need to put as a Slicer, the one from the calendar Table or "CreationDate"?

[Anonymous]

---

@Isra_BI wrote:

I've already created a Calendat Table with the following setup:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ))

So, which are the following steps now? I've got to create a relationship between column "Date" (calendar Table) and the column "CreationDate"?

Which column do I need to put as a Slicer, the one from the calendar Table or "CreationDate"?

---

Date =
ADDCOLUMNS (
CALENDAR (DATE(2013,1,1), DATE(2015,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

[MattAllington]

Yes, create the relationship as you say.  From then on, only ever use date column from your calendar table (you can hide the other one to avoid confusion).  Then write the formula I posted earlier (use the correct name of your calendar table).

[pooresj]

Hi @MAllington

Have tried to replicate your year on year bar chart, with the only difference that my data is structured as a single sheet (one column = date of sale, the other column = sales amount).

I created "Total Sales", "Total Sales LY" a new column for "month". I can create a bar chart like the one here with 'total sales' and 'total sales LY', but when making the month by month chart, 'total sales LY' is blank.

Do you have any ideas?

Thank you.

[MattAllington]

do you have a calendar table?  That is the first place to start

http://exceleratorbi.com.au/power-pivot-calendar-tables/

[greggyb]

1. DAX's built-in time intelligence functions only work with a standard calendar year, or with a fiscal year that uses standard calendar months/days, but has an alternate end-date (e.g FY starts July 1).
2. @MattAllington has shown the standard format for creating time intelligence functions for alternate calendars, utilizing FILTER() and ALL().
3. In Power BI, we're missing the 'Mark as Date Table' functionality, which allows us to use date dimensions with arbitrary keys. For the built-in time intelligence functions to work in PBI, you must join on Date fields. You can't join on an integer key. The relationship between fact and date dimension must be on the same field that you reference in time intelligence functions. See my detailed response in another thread to understand more.

[MattAllington]

@greggyb is there any documentation about the need to create the join on the date key?

[AliSharifi]

I used the date fields to join and now can see the diffrence as Matt mentioned, In my own datawarehouse models I use date data type  for datekey as it just takes 3 bytes instead of 4 bytes for int, so this should be fine for me. But i I think this  is a kind of lack of functionality when you can't use the inetegers for joinning.

[MattAllington · Accepted solution]

I'm glad it is working.  

A couple of points.

1. You can join on integers, it is just that you can't use the inbuilt time intelligence (something I didn't know until @greggyb mentioned it here).  You can of course write a formula using FILTER like I showed earlier.  I agree this should be changed and I am sure it will.
2. In Power Pivot/Power BI, data type is not very important because Power Pivot manages the compression so well during data load using Run Length Encoding, Dictionary Encoding and Value Encoding.  So the byte size of the data type is less important.

[greggyb]

@MattAllington, not sure about documentation. This was actually pointed out to me by another forum member when I suggested someone mark their date table (I'm all hung up on Tabular - that's about the only thing we do development in at my company). This behavior is not replicated in any of the PP for Excel Tabular engines, nor in Tabular itself that I can recall seeing.

It may be a bug, or it may be a result of some new feature / functionality being developed. It is not a major concern for me, as we almost never use the built in time intelligence features. There are so many fiscal calendars at clients, and also bumping up against the edges of what the built-ins can handle. It's easier to just use the FILTER( ALL() ) pattern for everything and not have to remember two sets of edge cases, or two idioms of time intelligence. Also, the logic of FILTER( ALL() ) can be applied to logic on other dimensions; the built-ins have no use outside of DimDate.

As to what the purpose of 'Mark as Date Table' is, why we still need to name the date field in built-in TI functions, and some random stuff on the topic, here are some thoughts:

• The TI functions were created before 'Mark as Date Table' functionality - devs likely wanted to avoid breaking syntax modifications
• The TI functions can work on fairly arbitrary date fields. The problem is that they are written fairly rigidly, so work is often more like "work" in that they return a value, and it has meaning, but that meaning is often not what you want outside of the context of a date dimension. Sometimes you might want it. I can't think of any good examples right now, but I'm sure there's a case where TI functions on non-date dimension makes sense.
• Mark as Date table guarantees that your date dimension fits the requirements of the expected use case of the TI functions (consecutive, non-repeating dates). Before we got date table marking, the suggested best practice was to join on date to at least guarantee the non-repeating requirement of the date field (lookup table must be the 1 side of N:1).
• The implicit ALL() filter is useful in some cases when we're writing DAX queries. I have two samples (that are unfortunately very difficult to break out from the client's logic, else I'd share some snippets) where the query is absurd if the date dimension is not marked, but is pretty reasonable when it is marked.

[MattAllington]

The issue is that you have the relationship on the Date Key and you are using FullDateAlternateKey in the formula.  Change the formula to refer to DateKey

[AliSharifi]

DateKey and OrderDateKey are integer that have data like 20070822. There is nothing wrong with them.

[MattAllington]

I can't help from here unless you post the workbook. 

[AliSharifi]

Sorry Matt, What do you mean of Workbook? do you mean the PowerBI Desktop file?

[MattAllington]

Yes, that's what I mean - sorry, it's an Excel hangover. 

[MattAllington]

So how about posting the workbook