Forum Discussion

ContabilidadBI's avatar
ContabilidadBI
Helper III
9 years ago
Solved

Problem with DATESMTD

Hello guys,   I am new to power BI and I have been struggling to make a simple month to date sales card for a few hours. Here is the formula:   MTD de Total = CALCULATE(SUM(Albaranes[Total]);DATE...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Your english is great!

     

    *if* you calendar stopped at the end of May... this would work fine.

     

    Ignoring that, you can add a calc column on your calendar table:

    IsCurrentMonth = IF (MONTH(TODAY()) = MONTH(MyCalendar[Date]), TRUE(), FALSE())

     

    Then set a filter on the card visual to IsCurrentMonth = TRUE  ?

ContabilidadBI's avatar
ContabilidadBI
Helper III
9 years ago

Hi Scottsen, thanks for your reply.

 

In the sales table, the date is just a date without time.

 

I have been thinking that maybe there is something else I need to put in the formula that will indicate that I want the sum of sales for the current month (May), something like today(), month()....

 

Because I keep getting blank as output and I do have sales in May in the sales table, the output should be something like 10k.

 

This is driving me nuts!

 

Thanks for the help.

 

 

ContabilidadBI's avatar
ContabilidadBI
Helper III
9 years ago

Hi,

 

I was trying again and If a put a month slicer in the same page of the report and filter for may, then yes, the card shows the sales amount for may until today. But what I want is a card of the running sales of THIS MONTH, which right now is May but it should change to 0 in the first day of June.

  • ContabilidadBI's avatar
    ContabilidadBI
    Helper III
    9 years ago

    Thats it Scootsen! uff at least I know now what was going on.

    • ContabilidadBI's avatar
      ContabilidadBI
      Helper III
      9 years ago

      So, what would be the measure formula that will always calculate the sum of sales for the month we are in?  that formula you put would be a filter parameter of the CALCULATE fuction I put in my first message? I can do the calculated column for this month, but I prefer if I can do it with only a measure.

       

      I have a card that shows the sales of the month in a dashboard, but when next month starts, I have to filter the card again, upload it to Power BI service and delete the other one, so what I am trying to achieve is a card that will automatically update when the month finish and the next one starts

       

      How would be the formula for the calculated column THIS MONTH btw?

       

      Thank you so much!!!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Actually... In case we are lucky...   I assume your calendar table is going "into the future" -- it doesn't stop at THIS month.   Could that be changed?  In which case... your current MTD would magically work?