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 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]))




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

8 Replies

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