Forum Discussion

AlexanderAT's avatar
AlexanderAT
Icon for Helper I rankHelper 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 actua...
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

v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Microsoft Employee rankMicrosoft 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

    • AlexanderAT's avatar
      AlexanderAT
      Icon for Helper I rankHelper I
      7 years ago

      Works perfect. Thank you!