Forum Discussion

RonSwanson's avatar
RonSwanson
Frequent Visitor
5 years ago

Current Month Sales Calculation Date Field YYYYQMM

Hi All, 

 

Brand new to Power BI (my skills are in Qlik Sense, but our company is moving away from it).  

 

I am trying to create a measure for current month sales.  I've done quite a bit of research but can't seem to get it to work.  I believe it is because the way the date field is formatted ('YYYYQMM').  Appreciate any help.  

 

Need Help

4 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    You could do as:

    Measure = 
VAR __CurMo = VALUE(YEAR(TODAY())&QUARTER(TODAY())&FORMAT(MONTH(TODAY()),"00"))
RETURN
CALCULATE(
    SUM('Table'[value]),
    FILTER(
        'Table',
        'Table'[month_processed_key] = __CurMo
    )
)

     

  • RonSwanson's avatar
    RonSwanson
    Frequent Visitor
    5 years ago

    Thanks for the quick reply.  I am having trouble getting it to work.  Here is some more data/info.  Sales column comes from Table named 'Sales' and Date column comes from a table called 'Calendar'. I assume I am not using the right table names for the Filter piece, but again a noob in Power BI.  

     

    thanks,

     

     

     

    • ChrisMendoza's avatar
      ChrisMendoza
      Icon for Resident Rockstar rankResident Rockstar
      5 years ago

      RonSwanson - Based off the data you're showing, you would not receive any results because [month_processed_key] <> __CurMo.

      __CurMo = 2021103

      Also, VALUE is used because the other functions return TEXT as my assumption is that the datatype for [month_processed_key] is number.

      Which part is not working? Are you receiving error messages? Can you provide a sample so I can know which datatypes are being used?

      Definitely looking to get you going because I have not forgot how difficult learning something new is however we all need more details otherwise we're just guessing at what you have.

      https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1521338

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi RonSwanson ,

     

    What does your measure want to calculate? Please share your sample data to me.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.