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...
  • 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..