Forum Discussion

mgirou's avatar
mgirou
Helper II
8 years ago
Solved

Date Graph issue

Hello,

 

I've been spending a lot of time trying to figure this out, but i just can't seem to find a way. Right now we use a simple KPI (revenue/hours) and i am trying to show KPI/Month but only for the last 12 months. The ISSUE is that for every month indicated on the X-Axis should be 12 months prior to that month.

 

for example right now it should be October 2016 to October 2017. for February 2017 i need my measure to grab (February 2016 to February 2017) data. Right now with my measure just Revenue/Hours i just grabs the KPI for the month displayed.

 

Any help would be great. I hope i gave enough details.

 

Need Help
  • mgirou's avatar
    mgirou
    8 years ago

    Actually i found the solution, but i can't explain why i am hoping someone can shed light on why this worked. By using that formula i just delete the ,date at the end of each reference to Calendar'[Date].[Date]. Can anyone explain why this fixed it?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi mgirou,

     

    I think the .[Date] changes the context of the formula. That's why the result is wrong. Have a look at the picture, the context of the visual is Date while it's [Date] in the formula, which is similar with [Day], [Month], etc. 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Please mark your answer as the solution.

     

     

    Best Regards!

    Dale

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi mgirou,

     

    Try a formula like this please. You need a date table.

    QuantityInLastMonth =
CALCULATE (
    SUM ( 'Sales'[Quantity] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -1, YEAR )
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • mgirou's avatar
      mgirou
      Helper II
      8 years ago

      Thank you v-jiascu-msft , but i have tried this formula and it doesnt give me the right numbers. I do have a date table.

       

      This is my formula (KPI test = CALCULATE(SUM('projectdata task_invoicing_lines'[Total Revenue]),DATESINPERIOD('Calendar'[Date].[Date],LASTDATE('Calendar'[Date].[Date]),-1,YEAR))/CALCULATE(SUM('projectdata employeehours'[Hours]),DATESINPERIOD('Calendar'[Date].[Date],LASTDATE('Calendar'[Date].[Date]),-1,YEAR)))

       

      What i do to compare this is just manually adjust a date for a certain month (lets say april) and i get the data from april1 2016 to april 1 2017 = 30.16  and that number doesn't match what i have for april with this formula.

       

      Please let me know if theres anything else i can try.

       

      Thanks

      • mgirou's avatar
        mgirou
        Helper II
        8 years ago

        Actually i found the solution, but i can't explain why i am hoping someone can shed light on why this worked. By using that formula i just delete the ,date at the end of each reference to Calendar'[Date].[Date]. Can anyone explain why this fixed it?