Date Graph issue

[mgirou]

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.

[mgirou · Accepted solution]

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]

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]

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 · Accepted solution]

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 · Accepted solution]

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