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  ?

MattAllington's avatar
MattAllington
Community Champion
9 years ago

What is in the Fecha column?  It needs to be a date type for it to work

  • ContabilidadBI's avatar
    ContabilidadBI
    Helper III
    9 years ago

    Hi Matt, thanks for your answer.

     

    Fecha is Date, and it has Date format, is the primary key of the Calendar Table. I have used other time inteligence fuctions such as PREVIOUSMONTH() without any problem.

     

    Here you can see the Calendar table:

     

     

    Thanks.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      My best guess is that in your fact table, you have dates WITH time -- which won't match up w/ your calendar table, which doesn't have times.   If that is the case, you need to strip off the times in the fact table.

      • 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.