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[pai...
"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

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 🙂

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    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