This can be confusing for Power BI users. When writing the SAMEPERIODLASTYEAR function (or TOTALYTD or something like this…), them sometimes (but not always) you have to add the third argument, the ALL function.

So when yes and when no?

Measures working with time intelligence functions can be created using two approaches. Either you can use the date column in the same table as calculated values, or you can work with column in a table connected with relation.

First option – date column from the same table

Calculated value and the date column are together in one table. This can be used for simple data models.

Then you have to use the ALL. The syntax looks like this:

  • Last year = CALCULATE(sum(‘Long data'[Price]);SAMEPERIODLASTYEAR(‘Long data'[Date]);ALL())

Second option – use of date from connected time dimension

Date, used as argument for SAMEPERIODLASTYEAR, is in connected date dimension.

Now you don´t need the ALL function, so the syntax can be like this:

  • Last year = CALCULATE(sum(‘Long data'[Price]);SAMEPERIODLASTYEAR(‘Time dimension'[Date]))

To put it simply

If you have a time dimension, you don´t need ALL. If you don´t have time dimension, you need ALL.

Leave a Reply

Your email address will not be published.


clear formPost comment