Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
6 years ago
Solved

Status Formula

Hi there ;

 

i need DAx formula for below issue thanks for your kind supports 

 

i have a table as below Delivery date and Quantity , i need to create a new column which show the status as i wanted.

 

so the rule will be like that :  

 

Our current  month is  : July 2020 

 

İf delivery date is in the current month  status will be "actual month"

 

if delivery dates will be before current month status  will be  "Previous Month " 

 

İf delivery date will be after current month Status will be " Next Month" 

 

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is one way to do it with a DAX calculated column.  Replace with your actual Table[Column] name that has your dates.

     

    Status of Month =
VAR todaysdate =
    TODAY ()
VAR mindate =
    DATE ( YEAR ( todaysdate ), MONTH ( todaysdate ), 1 )
VAR maxdate =
    EOMONTH ( TODAY (), 0 )
RETURN
    SWITCH (
        TRUE (),
        Sales[SaleDate] < mindate, "Previous Month",
        Sales[SaleDate] > maxdate, "Next Month",
        "Actual Month"
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Here is one way to do it with a DAX calculated column.  Replace with your actual Table[Column] name that has your dates.

     

    Status of Month =
VAR todaysdate =
    TODAY ()
VAR mindate =
    DATE ( YEAR ( todaysdate ), MONTH ( todaysdate ), 1 )
VAR maxdate =
    EOMONTH ( TODAY (), 0 )
RETURN
    SWITCH (
        TRUE (),
        Sales[SaleDate] < mindate, "Previous Month",
        Sales[SaleDate] > maxdate, "Next Month",
        "Actual Month"
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi erhan_79 ,

     

     

    Create a Calculated Column

     

     

    Column =
VAR _month =
    MONTH ( Table[DeliveryDate] )
VAR _year =
    YEAR ( Table[DeliveryDate] )
VAR _monthcurrent =
    MONTH (
        TODAY ()
    )
VAR _yearcurrent =
    YEAR (
        TODAY ()
    )
RETURN
    SWITCH (
        TRUE (),
        _year < _currentyear, "Previous Month",
        _year = _yearcurrent
            && _month < _monthcurrent, "Previous Month",
        _year = _yearcurrent
            && _month = _monthcurrent, " Actual Month",
        "Next Month"
    )

     

     

    Regards,
    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!