Forum Discussion

campelliann's avatar
campelliann
Post Patron
3 years ago
Solved

DAX Problem

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 equa...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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's avatar
    campelliann
    3 years ago

    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's avatar
johnt75
Super User
3 years ago

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's avatar
campelliann
Post Patron
3 years ago

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