- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is in the Fecha column? It needs to be a date type for it to work
* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thats it Scootsen! uff at least I know now what was going on.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Scottsen, don't really understand your last reply. My Calendar table goes until 12/31/2017 so until the end of the year.
English is not my first language.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This worked for me, i just don't know why..
DATESQTD() and DATESTYD(,) worked fine...... why not month??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks man, I learned a lot today!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ooohh!
MTD doesn't respect the current date at all. It's relative to the current filter context. If you go create a table w/ every day on it... you will see your measure working... growing the value each day, until end of month... when it resets to 1st day of month again.
You would need to do some sort of FILTER(ALL(Calendar), Calendar[Date] > TODAY() - DAY(TODAY()) + 1)
or probably easier to add a calc column to your calendar table IsCurrentMonth and use that to filter.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oh this makes so much sense now
Cheers!

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
11-29-2024 04:27 AM | |||
11-26-2021 01:22 AM | |||
12-19-2024 08:49 AM | |||
09-12-2024 11:40 PM | |||
12-20-2024 03:33 AM |
User | Count |
---|---|
141 | |
112 | |
83 | |
63 | |
47 |