DAX Problem

[campelliann]

Hi, 

So I have this table Called "DEAL", with my company opportunities. So we have a field in euros called opportunity, and the begining and end date of the project. The opportunity should be equally divided by the days in which the project will endure. So if we have 2000 euros over 2 months, it should be 1000 for each. I want a chart that in x axis there is the year-month, and y the sum of opportunities for the various projects. So I basically iterate over DEAL table, create a virtual table, and sum. The thing is I am getting the same total every month. Calendar table cannot filter DEAL, 

Here is what I have tried:

JRRevenue per month =

SUMX(DEAL,

VAR Meses_work= CALCULATETABLE(values(Calendario[Date]),Calendario[Date]>=EARLIER(DEAL[BEGINDATE])&&Calendario[Date]<=EARLIER(DEAL[CLOSEDATE])) ----- this gets the dates in which the project is occurring

VAR new_tab= ADDCOLUMNS(Meses_work,"@Division",EARLIER(DEAL[OPPORTUNITY])/countrows(Meses_work)) -- new column called division, that has the revenue permonth

Return sumx(new_tab,[@Division]))

[johnt75 · Accepted solution]

Try

JRRevenue per month =
VAR BaseDates =
    VALUES ( Calendario[Date] )
RETURN
    SUMX (
        DEAL,
        VAR BeginDate = DEAL[BEGINDATE]
        VAR CloseDate = DEAL[CLOSEDATE]
        VAR Opportunity = DEAL[OPPORTUNITY]
        VAR Meses_work =
            FILTER (
                BaseDates,
                Calendario[Date] >= BeginDate
                    && Calendario[Date] <= CloseDate
            )
        VAR new_tab =
            ADDCOLUMNS (
                Meses_work,
                "@Division", DIVIDE ( Opportunity, COUNTROWS ( Meses_work ) )
            ) -- new column called division, that has the revenue permonth
        RETURN
            SUMX ( new_tab, [@Division] )
    )

[johnt75]

Try

JRRevenue per month =
SUMX (
    DEAL,
    VAR Meses_work =
        CALCULATETABLE (
            VALUES ( Calendario[Date] ),
            KEEPFILTERS (
                Calendario[Date] >= EARLIER ( DEAL[BEGINDATE] )
                    && Calendario[Date] <= EARLIER ( DEAL[CLOSEDATE] )
            )
        ) ----- this gets the dates in which the project is occurring
    VAR new_tab =
        ADDCOLUMNS (
            Meses_work,
            "@Division", EARLIER ( DEAL[OPPORTUNITY] ) / COUNTROWS ( Meses_work )
        ) -- new column called division, that has the revenue permonth
    RETURN
        SUMX ( new_tab, [@Division] )
)

[campelliann]

It seems almost there @johnt75, and thank you.

When I do the return, it seems the formula is not recognizing the x-axis calendar date, as being the same date in the new tab table/variable. Thus the x axis is not filtering the virtual table new_tab, and I am getting 81000 for this project in both months instead of 81000/2...

[johnt75]

Instead of returning the result can you get it to return COUNTROWS(Meses_work). Just want to check if those dates are being filtered correctly or not.

[campelliann]

With this particular ID filtered is giving me 59. Which is the number of days between beginning and end. Dont understand... this is driving me crazy lol @johnt75 

[johnt75 · Accepted solution]

Try

JRRevenue per month =
VAR BaseDates =
    VALUES ( Calendario[Date] )
RETURN
    SUMX (
        DEAL,
        VAR BeginDate = DEAL[BEGINDATE]
        VAR CloseDate = DEAL[CLOSEDATE]
        VAR Opportunity = DEAL[OPPORTUNITY]
        VAR Meses_work =
            FILTER (
                BaseDates,
                Calendario[Date] >= BeginDate
                    && Calendario[Date] <= CloseDate
            )
        VAR new_tab =
            ADDCOLUMNS (
                Meses_work,
                "@Division", DIVIDE ( Opportunity, COUNTROWS ( Meses_work ) )
            ) -- new column called division, that has the revenue permonth
        RETURN
            SUMX ( new_tab, [@Division] )
    )

[campelliann]

The problem is more or less solved, I will consider this as the solution although I needed to do some changes...

[campelliann · Accepted solution]

@johnt75 sorry. I am getting constant values still. Although I was mistaken, the countrows (meses_work) is giving me 1 for a day instead of the full amount of days. The variable is not giving the supposed duration of the project, but the dates visibile in the filter context.

One last shot? I dont want to make you lose time. I will go with power query pivot/unpivot solution, if it this does not work..

[johnt75]

How are you summarizing the date? By day or by month ? Because by day will not work, it will always just divide by 1.