A Different Modeling Approach? Or A Different DAX Approach?

Power BI
1

Greetings,

I’m attempting what seems simple: A customer starts an incentive program on a particular date. I need to calculate their consumption one year prior to program start and one year post program start to evaluate program effectiveness. Here’s the DAX for the Prior Year calculation:

Program Consumption - Prior Year = 
    CALCULATE(
        [Total Usage],
        DATESBETWEEN('Consumption'[Date], 
                     DATEADD('Program Participants'[Program Start Date], -1, YEAR), 
                     'Program Participants'[Program Start Date]),
        ALLEXCEPT('Consumption', 'Consumption'[Account#])
    )

The error I’m getting is this:
DAX Error

Is the error happening because the Program Start Dates are not unique in the Program Participant table? If so, what approach can I take? The data set will have customers who enroll in multiple programs and those programs may have the same start date or different start dates. Thank you.

Program Effectiveness Example.pbix (91.2 KB)
Program Effectiveness.xlsx (19.9 KB)

2

@RandyS DATESBETWEEN need single value in second and third argument whereas you are supplying a table, also you should use Date column from date table in first arguments on DATESBETWEEN and DATEADD

1 Like
3

Understood. Fundamentally, however, when I have a filtering table of dates, and an Account# can have multiple Program Starting Dates, how should this be handled?

4

This revised DAX code does not throw an error, however, the result is incorrect. It is filtering from the MIN date in the table for every account, which is what I’m telling it to do, but I need the filtering to go back one year from the MIN start date of each account. Does this need to be modeled differently?

Program Consumption - Prior Year = 
VAR ProgramStartDate = MIN('Program Participants'[Program Start Date])
RETURN
    CALCULATE(
        [Total Usage],
        DATESBETWEEN(Dates[Date], DATEADD('Dates'[Date], ProgramStartDate -1, YEAR), 
                     ProgramStartDate)
    )

The result of this DAX creates this output:
Screenshot 2023-03-14 141529

5

Bumping this post for more visibility from our experts and users.

6

Hi @RandyS

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

In light of this, we will be closing this thread, but we invite you to start a new thread with updated details so that our experts can better assist you.

Thank you for being a part of our community, and we hope you find the help you need.