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

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

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

        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