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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    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 )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
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 )