Forum Discussion

TutanRamon's avatar
TutanRamon
Frequent Visitor
1 year ago
Solved

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? 

 

"Date"
reuse
  • OwenAuger's avatar
    OwenAuger
    1 year ago

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

    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

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

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

    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

    • TutanRamon's avatar
      TutanRamon
      Frequent Visitor
      1 year ago

      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.

    • TutanRamon's avatar
      TutanRamon
      Frequent Visitor
      1 year ago

      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()
)
      • OwenAuger's avatar
        OwenAuger
        Super User
        1 year ago

        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 🙂