Dax function to Convert Sales Target to Daily Target

Question

I have a table where each Organization has a monthly sales target define by a Start and End date. An active sales target has a start date and a null end date (See OrgId 1). When a new sales target is created the end date is set on the old record and a new record with new start date and target (See OrdId 2).

OrgId	Start Date	End Date	Target
1	1/1/2018	Null	10000
2	1/1/2018	12/31/2018	12000
2	1/1/2019	Null	14000

I need to convert this to a new table of daily targets for each Organization from the start date to the end date. When the end date is null, then the end date should Coalesce to Todays date + 365.

Can this done using Dax in Power BI?

Joao Leal Joao Leal · Accepted Answer · 2020-12-10T01:10:07

First you need to have a table with all the days, check this link for an example in DAX.

Once you have the date table, you can define a measure for your Daily Target like this:

Org Daily Target (next 365days)= 
VAR selectedDate = SELECTEDVALUE(DateTable[date])
VAR selectedOrganization = SELECTEDVALUE(OrgTable[OrgID])
VAR today = TODAY()

RETURN 
    IF(selectedDate < today || selectedDate > today + 365, BLANK(),
    CALCULATE(FIRSTNONBLANK(OrgTable[Target]), 
                OrgTable[OrgId] = selectedOrganization,
                OrgTable[StartDate] <= selectedDate,
                (selectedDate = today + 365 && ISBLANK(OrgTable[EndDate]) 
                   || OrgTable[EndDate] >= selectedDate
              )
      )

Dax function to Convert Sales Target to Daily Target

1 Answers