Forum Discussion

Ainala's avatar
Ainala
Frequent Visitor
4 years ago
Solved

Need Help with Current Month, Total year DAX

Hello Power Users,   I'm struggling with some dax measures. I have 2 slicers from Date table ( Year and Month). I would like to create measures for Month(First Day of the month-Last Day of the mont...
  • Jihwan_Kim's avatar
    4 years ago

    Hi,

    Please check the attached pbix file below.

    I tried to create a sample pbix file, and the data model looks like the below.

    All measures are in the attached pbix file.

     

     

    Current month orders: = 
    VAR _currentyear =
        MAX ( 'Calendar'[Year] )
    VAR _currentmonth =
        MAX ( 'Calendar'[Month] )
    VAR _lastdateofthemonth =
        CALCULATE (
            MAX ( 'Calendar'[Date] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year] = _currentyear
                    && 'Calendar'[Month] = _currentmonth
            )
        )
    VAR _periodtable =
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= _lastdateofthemonth
                && 'Calendar'[Year] = _currentyear
                && 'Calendar'[Month] = _currentmonth
        )
    RETURN
        CALCULATE ( SUM ( Data[Order] ), _periodtable )

     

    Current year  upto current month orders: = 
    VAR _currentyear =
        MAX ( 'Calendar'[Year] )
    VAR _currentmonth =
        MAX ( 'Calendar'[Month] )
    VAR _lastdateofthemonth =
        CALCULATE (
            MAX ( 'Calendar'[Date] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year] = _currentyear
                    && 'Calendar'[Month] = _currentmonth
            )
        )
    VAR _periodtable =
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= _lastdateofthemonth
                && 'Calendar'[Year] = _currentyear
        )
    RETURN
        CALCULATE ( SUM ( Data[Order] ), _periodtable )

     

    Current year all orders: = 
    VAR _currentyear =
        MAX ( 'Calendar'[Year] )
    VAR _lastdateoftheyear =
        CALCULATE (
            MAX ( 'Calendar'[Date] ),
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Year] = _currentyear
            )
        )
    VAR _periodtable =
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= _lastdateoftheyear
                && 'Calendar'[Year] = _currentyear
        )
    RETURN
        CALCULATE ( SUM ( Data[Order] ), _periodtable )