Forum Discussion

TomBurke's avatar
TomBurke
Regular Visitor
6 years ago

Weekly Comparison vs Previous Year into KPI Visual

Hello there community,   I'm here today seeking your help with time inteligence and creating a KPI Visual with the results. Currently, I have two KPI's, one for Monthly Profit MTD and Weekly Profi...
Need Help
TomBurke's avatar
TomBurke
Regular Visitor
6 years ago

Hi Allison,

 

Thanks very much for your reply.

I had previosuly tried all 3 versions, but I went back and it seems that DATEADD works fine.
However, I am getting fictisious results for Week 18 & 19.
(Unable to post images due to Data Protection).

 

I have been trouble-shooting in my data and have explored it in Excel and the figures shown using DateAdd are incorrect.

Is there anything you can suggest that might cause an issue with 2 of 19 results?
Seems very strange.


Many thanks,

 

 

 

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi TomBurke ,

In essence, DATEADD expects a table column of dates as first parameter as most of Time Intelligence functions, which are designed to work with Date/Calendar tables (Date dimensions).

For example, you have this table called MyDates

If you use DATEADD to create a calculated column called 4MonthsAfter:

 

4MonthsAfter = DATEADD(MyDates[Date],4,MONTH)

 

It returns this:

As you can see the column 4MonthsAfter for the date 24/08/2016 value is blank since the column doesn't contain 24/12/2016 value, the same happens for 24/01/2017 because there is no 24/05/2017 value. But 4 months after 24/09/2016 is computed since 24/01/2017 is present in the column.

 

In addition, you can refer this article about difference between Dateadd, ParallelPeriod and SamePeriodLastYear:

DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • TomBurke's avatar
    TomBurke
    Regular Visitor
    6 years ago

    Hi v-yingjl ,

     

    Thanks for the link - however this still doesn't shed much light on a solution to this issue.

     

    I am starting to feel that perhaps this visual is unachieveable as a KPI, and may be better suited to something such as a Matrix? (Not ideal as this means showing more data than required, but workable.)

    I am simply trying to take last years figures, and use them as this year's target. 

    I did not know this would be so difficult.