Current Month Sales Calculation Date Field YYYYQMM

[RonSwanson]

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.  

 

[v-stephen-msft]

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.

[RonSwanson]

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]

@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

[ChrisMendoza]

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
    )
)