Forum Discussion

AlexanderAT's avatar
AlexanderAT
Helper I
7 years ago
Solved

Datesbetween - need help with dax measure

Hello

 

I think what I need is the datesbetween measure, but if you have another suggestion I'll try it. 

 

I need to calculate turnover-budget for this year. I don't want it to include the actual month as it has not yet generated the full turnover. Is it possible to make a measure that calculates the start of the year to and including the last calendar month? 

 

Where I am so far: 

Budget Target = CALCULATE([turnover]-SUM('BoligPortal Turnover'[Budget]),DATESBETWEEN('Date'[Date],

Need Help
Show and Tell
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi AlexanderAT,

    Based on my test, you could refer to below formula:

    Measure = 
CALCULATE (
    SUM ( Table1[Turnover. FBP] ),
    FILTER (
        'Table1',
        MONTH ( 'Table1'[YearMonthShort] ) < MONTH ( TODAY () )
            && YEAR ( 'Table1'[YearMonthShort] ) = YEAR ( TODAY () )
    )
)
    - CALCULATE (
        SUM ( Table1[Budget - FBP] ),
        FILTER (
            'Table1',
            MONTH ( 'Table1'[YearMonthShort] ) < MONTH ( TODAY () )
                && YEAR ( 'Table1'[YearMonthShort] ) = YEAR ( TODAY () )
        )
    )

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi AlexanderAT,

    Based on my test, you could refer to below steps:

    Sample data:

    Measure:

    Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1',MONTH('Table1'[Date])<MONTH(TODAY())))

    Result:

    If  I have misunderstand you, could you please share the sample data and post your desired result if possible?

     

    Regards,

    Daniel He

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi AlexanderAT,

        Based on my test, you could refer to below formula:

        Measure = 
CALCULATE (
    SUM ( Table1[Turnover. FBP] ),
    FILTER (
        'Table1',
        MONTH ( 'Table1'[YearMonthShort] ) < MONTH ( TODAY () )
            && YEAR ( 'Table1'[YearMonthShort] ) = YEAR ( TODAY () )
    )
)
    - CALCULATE (
        SUM ( Table1[Budget - FBP] ),
        FILTER (
            'Table1',
            MONTH ( 'Table1'[YearMonthShort] ) < MONTH ( TODAY () )
                && YEAR ( 'Table1'[YearMonthShort] ) = YEAR ( TODAY () )
        )
    )

        Result:

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He