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.

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