Forum Discussion

shanipowerbi's avatar
shanipowerbi
Icon for Helper III rankHelper III
5 years ago
Solved

Need Month 1st date value in each row

Hi Expert   I need month 1st date value each day, for good understanding please see example below:   Date                Amount      1st Date Amount (required this) 01 Jan 2020    80,225        ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, shanipowerbi 

     

    Please try the below measure.

     

    sales first day of month =
    VAR currentyear =
    YEAR ( MAX ( Sales[Date] ) )
    VAR currentmonth =
    MONTH ( MAX ( Sales[Date] ) )
    VAR currentmonthfirstday =
    DATE ( currentyear, currentmonth, 1 )
    RETURN
    CALCULATE ( Sales[sales], Sales[Date] = currentmonthfirstday )

     

     

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

    Linkedin: https://www.linkedin.com/in/jihwankim1975/

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, shanipowerbi 

    I am not sure how you changed the context, but if it is OK with you, please share your sample pbix file's link, then I can try to look into it to come up with a more accurate measure.

     

    Thank you.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi,

    please share your sample pbix file's link, then I can try to look into it.

     

    Thank you.

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

shanipowerbi , with help from date table

 

calculate(sum(Table[Amount]), datesmtd(Date[Date]), filter(Date, Date[Date] = eomonth(Date[Date],0)+1))

 

or

 

calculate(sum(Table[Amount]), filter(allselected(Table), Table[Date] = eomonth(Table[Date],0)+1))