Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX to Check Current Month and Return a Value

  • 1.  DAX to Check Current Month and Return a Value

    Bronze Contributor
    Posted 4 days ago
    Edited by marek bujarski 4 days ago
    Hello, I am working on an DAX problem and need some help,

    Can I use the DAX Month(TODAY) Function with an IF statement?  So, I need to verify if we are in the current month, then return the value from a measure?

    I need to figure out how to write a DAX formula that checks what current month we are in and based on that return a value, such as a measure.

    For example, let's say our fiscal year starts in June, so June, July and August would be Q1 of FY22.
    The logic needs to go like this, if we are in Q1, and June has not ended, then return Forecast for the entire Q1,

    But,

    If let's say June is over, then we need to return June Actuals + July Forecast + August Forecast, then the logic just repeats,

    If July is over, then return June Actual + July Actual + Aug Forecast into one number.  I have managed to figure this out but it requires manual intervention every month. So, I have to update the formula every month for this to work.

    The biggest hurdle is how to have DAX check what month we are in then return the correct measure, I wrote the measure below, that returns the previous months Actual Inflation, but how do I have DAX return this based on what month we are in.

    ** Returns last months actual inflation:

    FY last_month_actual_inflation =

    VAR current_month = MONTH(TODAY())

    RETURN CALCULATE(SUM('Inflation Actuals Monthly'[Value]),FILTER('Inflation Actuals Monthly',MONTH('Inflation Actuals Monthly'[YrMonth_Date].[Date]) = current_month -1))



    Thanks

    Marek



    ------------------------------
    marek bujarski
    Consultant
    DeKalb
    ------------------------------


  • 2.  RE: DAX to Check Current Month and Return a Value

    Top Contributor
    Posted 2 days ago


    Try this and see if you are looking for this calculation.

    FY last_month_actual_inflation =
    
    VAR current_month = DATE(Year(TODAY()),MONTH((TODAY())),1)
    
    RETURN CALCULATE(SUM('Inflation Actuals Monthly'[Value]),
    
    FILTER('Inflation Actuals Monthly',MAX('Inflation Actuals Monthly'[YrMonth_Date]) < current_month ))​


    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------