topic Re: Helper function for date comparison in DAX Commands and Tips

Helper function for date comparison

TutanRamon — Fri, 04 Oct 2024 11:04:00 GMT

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?

Re: Helper function for date comparison

OwenAuger — Fri, 04 Oct 2024 10:58:25 GMT

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:

Applying the calculation item as a filter in the report page.
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" )

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

Re: Helper function for date comparison

TutanRamon — Fri, 04 Oct 2024 11:31:48 GMT

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.

Re: Helper function for date comparison

TutanRamon — Fri, 04 Oct 2024 12:18:36 GMT

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() )

Re: Helper function for date comparison

OwenAuger — Fri, 04 Oct 2024 20:33:09 GMT

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%20item%20application

Re: Helper function for date comparison

OwenAuger — Fri, 04 Oct 2024 20:51:23 GMT

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 🙂

Re: Helper function for date comparison

OwenAuger — Sun, 06 Oct 2024 03:09:40 GMT

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:

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

Regards

Owen