Forum Discussion

JoseQ's avatar
JoseQ
Frequent Visitor
6 years ago

Measure TOTALQTD not working

I have two tables: 1. Sales by product ID 2. Time  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 ...
Need Help
jstorm's avatar
jstorm
Icon for Resolver III rankResolver III
6 years ago

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.

JoseQ's avatar
JoseQ
Frequent Visitor
6 years ago

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

 

  • jstorm's avatar
    jstorm
    Icon for Resolver III rankResolver III
    6 years ago

    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.

    • JoseQ's avatar
      JoseQ
      Frequent Visitor
      6 years ago

      Pleaes take a look.

       

      I see many inconsistencies within the data table:

      • May and June shouldn't have any values on 2020 QTD Sales Income measure (last sales record is on 26th april).
      • May thru December should have any values on 2020 YTD Sales Income measure (lasta sales record is on 26th april).
      • QTD Sales income measure should only account the sales from 1st Jan- 31st March and it's throwing the same value as YTD Sales Income measure (its adding values from 26 days of April).

      [CONFIDENTIAL INFO.]

      Please help.

       

      Thnks,

      JQ

       

       

      • karun_r's avatar
        karun_r
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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.