Forum Discussion

sokg's avatar
sokg
Icon for Solution Supplier rankSolution Supplier
9 years ago
Solved

Help with Line chart shows current TOTALQTD and Last Year same TOTALQTD

Hello to all,   I have  Sales = SUM(SalesValue). I have a date table with dates from 1/1/2016.   I have created QTDSales= TOTALQTD([Sales],DATESQTD(dCalendar[Date])) and PY QTDSales = CALCULATE(...
Need Help
  • sokg's avatar
    sokg
    9 years ago

    I fix it using these formulas:

     

    QTDSales = CALCULATE(TOTALQTD([Sales];DATESQTD(dCalendar[Date]));dCalendar[Quarter No]=ROUNDUP(MONTH(TODAY())/3;0);dCalendar[Year]=YEAR(TODAY()))

     

    and

     

    PY QTDSales = CALCULATE([Sales];DATEADD(DATESQTD(dCalendar[Date]);-1;YEAR);dCalendar[Quarter No]=ROUNDUP(MONTH(TODAY())/3;0);dCalendar[Year]=YEAR(TODAY())-1)

     

    Thank you all, for your replies.

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

sokg

 

can you provide a screen shot of your data that is wrong?

sokg's avatar
sokg
Icon for Solution Supplier rankSolution Supplier
9 years ago

I want to see only the last quarter. The part from 1/4/2017 till today.

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    9 years ago

    sokg

    QTD doesn't refer to the latest quarter as far as i know it just rolls it up at a quarter level

     

     

     

    • sokg's avatar
      sokg
      Icon for Solution Supplier rankSolution Supplier
      9 years ago

      Yes i know that, but i there any way to show only the last quarter?

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi sokg,

        You can create a new table including date column, then create a calculated column using the formula.

        last Quarter = CALCULATE(MAX('Newtable'[Quarter]),ALLEXCEPT('Newtable','Newtable'[Year]))

        Then create a measure get the min day of the last quarter in current year.

        Min day = CALCULATE(MIN('Newtable'[Date]),FILTER('Newtable','Newtable'[Year]=MAXX(ALL('Newtable'),'Newtable'[Year])&&'Newtable'[Quarter]='Newtable'[last Quarter]))

        Then create another measure using the formula.

        Year-to-quarter = CALCULATE(Sales[Year-to-Quarter],FILTER('Sales','Sales'[Date]>=Newtable[Min day]))

        To display part of the value, which is similar with the rolling days part in this thread.

        Best Regards,
        Angelia