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]);DATESMTD(Calendario[Fecha]))

 

And here is the fact table of sales and the Calendar table:

 

 

 

Now we are in May, so I should be getting the sum of sales amount since 1/5/2017 until today: 4/5/2017 .  Instead I am getting BLANK as the output. What am I doing wrong?

 

Thanks for all the help and learning I am getting from this forum!!

 

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  ?

14 Replies

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