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.

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
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/

 

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

    Thanks, it worked, but it's not working when I turn the date in to Hierarchy 

     

     

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      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
      Icon for Super User rankSuper User
      5 years ago

      Hi, 

      I just posted a reply to your other post.

      Please try the below.

       

      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], filter (all (Sales), Sales[Date] = currentmonthfirstday ))

       

      If you change the context from date to date-hierarchy, I think one more condition needs to be added to the measure.

       

      Thanks.

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

        Hi Kim

         

        Thanks for the help, applied your suggested formula its working but not giving 1st day value 

         

        I used this 

        1st Day Billing = VAR currentyear =
        YEAR ( MAX ( Billing[Date2] ) )
        VAR currentmonth =
        MONTH ( MAX ( Billing[Date2] ) )
        VAR currentmonthfirstday =
        DATE ( currentyear, currentmonth, 1 )
        RETURN
        CALCULATE ( Billing[Billing], filter (all (Billing), Billing[Date2] = currentmonthfirstday ))