Calculation of MTD sales

[Anonymous]

Hello All

I have a report tab which builds monthly sales and growth charts. 

I have a monthly sales column separately in the file as well. 

The issue is with the % sales growth in the card, it shows -98% and in the graph, it shows 20% (which is right ). I can't put a filter on the card visual-only because it will be a hassle for the end-user. 

Can someone tell me what's wrong?

i am not able to attach the picture which could help you a lot. 

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

the formula used is 

(Sales of current year - sales of previous year)/ sales of previous year 

Sales of previous year = 

CALCULATE(SUM('P&L Monthly'[Sales]),SAMEPERIODLASTYEAR(Dates[Date]))/1000

[edhans]

Your graph is being filtered by month. Your card is not, so it goes out into the future.

See this article on how to do this. You'll need a new measure for your card to exclude future dates. 

If you are always wanting it to show a specific month, relative month, you can set the filter on the card visual. Or you can do some filtering in a measure with FILTER() using dates and other dynamic data so it moves forward in time as you need it to.

[Anonymous]

@edhans Thank you so much for your response.

I cannot use month filter on the card visual because then the end-user has to change it again and again which will be a bit of a hassle for them as they are very basic users. 

Can you please elaborate this please, Or you can do some filtering in a measure with FILTER() using dates and other dynamic data so it moves forward in time as you need it to.

[amitchandak]

Please note time intelligence needs an end date for a few calculations. So when you plot it by month or any date related stuff, you are providing an end date.

So your bar visual gets it from month year.  Card visual is not having any, So It will take end date of the calendar table or the table who's date is used.

There are ways around when you do not want to give a date.

1. Stop your calendar table to today() or month-end

2. Use a visual level relative date filter and control it below today. When you use MTD or YTD from time intelligence end date is important so you can have anything below today. You can say the last two years in relative date. But if you use datesytd, totalytd , datesmtd and totalmtd. they will respond to end date. And filter on date in any other visual

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[edhans]

@Anonymous - let's try this. I got your PM. Rather than solving that way and I get why you are skittish on sharing confidential data. 100% understand.

This handles all future dates just fine. Note that in my Date table I have an IsFuture column. See the Power Query side in the Power BI file linked below on that column. Pretty simple, if the date is after today, future = true.

Take a look at the DAX in this PBIX file. It returns the following measures reliabily (I think - I had some switched around DATESYTD/MTD functions that I think are all fixed):

1. Total Sales
2. MTD Sales
3. YTD Sales
4. LMTD (last year MTD) Sales
5. LYTD Sales

Note that the techniques here are from this excellent SQLBI article that we should all commit to memory. The magic is in the CALCULATETABLE() logic.
THis should get you started. If you still have issues, please post back and let's see what is next. 

[edhans]

What are you expecting the card to show? The visual shows January as the latest data you want to see. But today is March 8. How is the card supposed to know what to show you?

If you can tell me that, and provide some good sample data, or your PBIX, I'll work on it, or someone else here can jump in. It will be a few hrs before I am back at my PC.

[Greg_Deckler]

Can you post your formula and perhaps some sample data that recreates the issue? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

sales growth YTD issueThis is my issue here. You see on the card it gives me -98% whereas if you see in the graph, it shows 20% which is right. 

The formula is the same for both of them. 

Filter on-page is year only. 

I want to show YTD sales growth on the card. Right now the data for other months in not uploaded for the current year. 

[dax]

Hi @Anonymous , 

It seems to be related to context, the card will show "total " level instead of each month. When you click on each month in chart, will the card changed ? By  the way, what result did you want to get? If possible, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.