Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have two tables:
There's a relationship between these two tables where cardinality is many to one (*:1) and cros filter direction is both. The field key is the date on both tables.
I've just created 3 measures: Year-to-date, Month-to-date and Quarter-to-date however the last one isn't working properly. QTD is showing the same values as MTD measure.
I tried both syntaxis por QTD measure both it yielded the same result.
Syntaxis 1: QTD Sales income = TOTALQTD(SUM(SalesbyProd[Sales income]),DATESQTD('Time'[Date])
Syntaxis 2: QTD Sales income = CALCULATE(TOTALQTD(SUM(SalesbyProd[Sales income]),DATESQTD('Time'[Date]),ALL('Time'[Date])))
Help please.
Is your sales data only being recorded once per quarter? For example,
3/1 $1000
6/1 $2000
9/1 $1500
12/1 $1000
Try putting your data into a table visualization without any measures to see if there are any inconsitencies or some underlying logic you are not considering.
My sales data is being recorder daily.
However my time table is comprised of difrente time scales: daily, weekly, monthly, bimestraly, quarterly, triannually and semestraly...
This for example:
[CONFIDENTIAL INFO.]
Set up a table visualization with Time[Month], Time[Quarter], Time[Year], and your YTD, MTD, QTD measures. Check to see if there are problems there and post a screenshot. Also, check your columns in Time and Sales to be sure they are the right data type.
Pleaes take a look.
I see many inconsistencies within the data table:
[CONFIDENTIAL INFO.]
Please help.
Thnks,
JQ
@JoseQ What you have is a valid behavior for QTD and YTD functions in DAX.
1. You will see number for May and June even though there is no revenue yet because they both are part of the same quarter that April is in and since we are using a QTD (Quarter to Date) measure, it will show the total revenue in the quarter so far. If you don't want to see the number for May and June, I suggest you have a column in your 'Date' table that will tell you which month is in future and based on that you can toggle between showing the number as is or a blank.
2. Same thing applies for YTD as well as it is Year to Date. You can apply the same logic here
3. For the first three months, QTD and YTD will be same. This is after assuming that Jan to March is the first quarter for your org.
Thank you. Im still not clear with some concepts but i'll keep on research.
I'm also requesting the approval to remove/delete all images/screenshots provided by myself during the discussion since this information is confidential. I can leave all the text in case some one else with the same issue can find an answer on this conversation.
The syntax should be...
TOTALQTD(
SUM( SalesbyProd[Sales income] ),
'Time'[Date]
)
Can you post a screenshot of your model? Can you post you MTD and YTD measure syntax?
hi, thanks for the fast response.
I tried changing the syntaxis but gave the same results.
By model you mean relationships if so.. this is the model..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |