Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
TutanRamon
Regular Visitor

Helper function for date comparison

I have many formulas which calculate a measure. For instace: sales, itemQty, returns, returnQty etc. etc. Basiscally, they all follow the same pattern. For instance:

 

itemSales = SUM(ob_sales[paidAmount])

 


In this report, I have a conditional 'last year' setup. When people select only 1 of 3 days, i need to shift back 7*52 days. When a month is selected (ie: aug 1 till aug 31), then I want to use SAMEPERIODLASTYEAR. So, the measure below is working fine:

 

itemSalesLY = 
VAR SelectedDatesCount = COUNTROWS(VALUES('calendar'[Date]))
VAR IsSmallSelection = SelectedDatesCount <= 27
VAR DateRangeLastYear = 
    CALCULATE(
        SUM(ob_sales[paidAmount]),
        DATEADD('calendar'[Date], -52*7, DAY)  
    )
VAR SalesLastYearExactDates = 
    CALCULATE(
        SUM(ob_sales[BetaaldExclBtw]),
        SAMEPERIODLASTYEAR('calendar'[Date])  
    )
RETURN
IF(
    IsSmallSelection,
    DateRangeLastYear,  
    SalesLastYearExactDates
)

 

This works like a charm, but as I said, I have many measures which need to use this LY date logic. So, I thought I'll create a lastYearDateHelper expression, but this is where it gets ugly.

Whatever i try, it always ends in errors like A function ‘PLACEHOLDER’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

The goal is to have something like this:

 

itemSalesLY = 
CALCULATE(
    SUM(ob_sales[paidAmount]),
    [LastYearDateHelper]
)

 

Is this even possible? 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @TutanRamon 

I would recommend using a calculation group for this instead. It's not possible to have a "measure" return a table expression then use that as a filter argument within CALCULATE.

 

You can create a calculation group containing a "Last Year Dynamic" calculation item that handles the two variations of "last year".

Here is the Microsoft guide on creating calculation groups. You can use either Power BI Desktop or Tabular Editor to create them.

 

I have attached an example PBIX containing one of my own models.

 

Your "Last Year Dynamic" calculation item should have an expression similar to this:

 

VAR SelectedDatesCount = COUNTROWS ( 'calendar' )
VAR IsSmallSelection = SelectedDatesCount <= 27
VAR Result =
    IF (
        IsSmallSelection,
        CALCULATE (
            SELECTEDMEASURE ( ),
            DATEADD ( 'calendar'[Date], -52 * 7, DAY )
        ),
        CALCULATE (
            SELECTEDMEASURE ( ),
            SAMEPERIODLASTYEAR ( 'calendar'[Date] )
        )
    )
RETURN
    Result

 

SELECTEDMEASURE() is a placeholder for any measure that the calculation item is applied to. Calculation items can only be applied to measures, not general expressions.

 

Once you've created a calculation group and calculation item, you can apply the calculation item to measures by either:

  1. Applying the calculation item as a filter in the report page.
  2. Applying the calculation item as a filter within a DAX expression.

Here is a report page showing both methods.

  • "Last year" relative to 1-Feb-2021 is 3-Feb-2020
  • "Last year" relative to Feb-2021 is Feb-2020

The measure Sales Amount Last Year Dynamic applies the "Last Year Dynamic" calculation item as follows:

CALCULATE (
    [Sales Amount],
    'Time Intelligence'[Time Calc] = "Last Year Dynamic"
)

OwenAuger_0-1728039177446.png

There is an alternative method where you can create table functions using DETAILROWS, but I wouldn't recommend it as it's not intended for this purpose. But you can read up on it here.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

Glad it works 🙂

The "explicit measure" requirement means that calculation groups only affect measures that are defined in the model. The measure must be included directly in the visual or the measure reference used directly in the DAX expression where the calculation item is applied.

Here is a good explanation:

https://www.sqlbi.com/articles/understanding-calculation-groups/#:~:text=Introducing%20calculation%2...


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @TutanRamon 

I would recommend using a calculation group for this instead. It's not possible to have a "measure" return a table expression then use that as a filter argument within CALCULATE.

 

You can create a calculation group containing a "Last Year Dynamic" calculation item that handles the two variations of "last year".

Here is the Microsoft guide on creating calculation groups. You can use either Power BI Desktop or Tabular Editor to create them.

 

I have attached an example PBIX containing one of my own models.

 

Your "Last Year Dynamic" calculation item should have an expression similar to this:

 

VAR SelectedDatesCount = COUNTROWS ( 'calendar' )
VAR IsSmallSelection = SelectedDatesCount <= 27
VAR Result =
    IF (
        IsSmallSelection,
        CALCULATE (
            SELECTEDMEASURE ( ),
            DATEADD ( 'calendar'[Date], -52 * 7, DAY )
        ),
        CALCULATE (
            SELECTEDMEASURE ( ),
            SAMEPERIODLASTYEAR ( 'calendar'[Date] )
        )
    )
RETURN
    Result

 

SELECTEDMEASURE() is a placeholder for any measure that the calculation item is applied to. Calculation items can only be applied to measures, not general expressions.

 

Once you've created a calculation group and calculation item, you can apply the calculation item to measures by either:

  1. Applying the calculation item as a filter in the report page.
  2. Applying the calculation item as a filter within a DAX expression.

Here is a report page showing both methods.

  • "Last year" relative to 1-Feb-2021 is 3-Feb-2020
  • "Last year" relative to Feb-2021 is Feb-2020

The measure Sales Amount Last Year Dynamic applies the "Last Year Dynamic" calculation item as follows:

CALCULATE (
    [Sales Amount],
    'Time Intelligence'[Time Calc] = "Last Year Dynamic"
)

OwenAuger_0-1728039177446.png

There is an alternative method where you can create table functions using DETAILROWS, but I wouldn't recommend it as it's not intended for this purpose. But you can read up on it here.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

How can I create a text, based on this calculated item measure? 

So, when people select Sep 1 till Sep 13, I want to show a text (in the header) which mentions "Compared to Sep 3 till Sep 15". I have this , but i aint'working. 

dateCompareText = 
VAR SelectedCalc = SELECTEDMEASURENAME()

VAR IsLastYearDynamic = SelectedCalc = "lastYearDynamic"

VAR MinCompareDate = CALCULATE(MIN('Date Logic'[dateCalc]), 'Date Logic'[dateCalc])
VAR MaxCompareDate = CALCULATE(MAX('Date Logic'[dateCalc]), 'Date Logic'[dateCalc])
VAR DateCount = CALCULATE(COUNTROWS(VALUES('Date Logic'[dateCalc])), 'Date Logic'[dateCalc])

VAR SingleDateText = 
    "Vergeleken met " & 
    FORMAT(MinCompareDate, "dddd d MMMM yyyy")  

VAR MultiDateText = 
    "Vergelijken met " & 
    FORMAT(MinCompareDate, "ddd d MMM yyyy") & " t/m " & 
    FORMAT(MaxCompareDate, "ddd d MMM yyyy") 

RETURN 
IF(
    IsLastYearDynamic, 
    IF(
        DateCount = 1, 
        SingleDateText,  
        MultiDateText    
    ),
    BLANK()
)

Could you post a screenshot of the visual where you want to display this text?

And just confirming, did you want to display this in the title of the visual?

 

Some adjustment to the code is needed regardless. I'll have a proper look when I have time later today 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi again @TutanRamon 

Here is one example of how you could set things up (updated PBIX attached):

 

1. Create a measure Date Range Text that just returns the selected date range formatted appropriately:

 

Date Range Text = 
VAR DateMin =
    MIN ( 'Date'[Date] )
VAR DateMax =
    MAX ( 'Date'[Date] )
VAR DateCount = COUNTROWS ( 'Date')
VAR Result =
    IF (
        DateCount = 1,
        FORMAT ( DateMin, "dddd d MMMM yyyy" ),
        FORMAT ( DateMin, "ddd d MMM yyyy" )
            & " t/m " & FORMAT ( DateMax, "ddd d MMM yyyy" )
    )
RETURN
    Result

 

 

2. Create a measure Date Compare Text that computes Date Range Text with the "Last Year Dynamic" calculation item applied and the prefix "Vergeleken met":

 

Date Compare Text = 
"Vergeleken met " &
CALCULATE (
    [Date Range Text],
    'Time Intelligence'[Time Calc] = "Last Year Dynamic"
)

 

3. Then you can use Date Compare Text as required, such as in visual Title:

OwenAuger_0-1728184087235.png

Variations on this are of course possible, but hopefully this is enough for you to go on with 🙂

 

Regards

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Wow, just wow 🙂 Thanks for the detailed answer.

It works. When I added the Calculation Group, I got a warning about implicit and explicit formulas. I get the difference, but how does it affect the calculation group? I mean, just doing SUM(x) (=implicit I believe) is still possible.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors