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

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?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        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