Weekly Comparison vs Previous Year into KPI Visual

[TomBurke]

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 Profit WTD.

Monthly Profit MTD is a KPI and it works flawlessly. 
I have a standard Total Profit Measure and a Previous Month Profit Measure -1 Year.
Dax Code: Previous Month Profit = CALCULATE([Total Profit] , SAMEPERIODLASTYEAR(Rolling_Calendar[Date]))

From what I can tell this is correct, and I have spotchecked it myself various times.

Now for what I am struggling with....
It seems that Power Bi does not have the in-built inteligence for a Weekly Statistic just like this.

I am looking for any kind of guideance or ideas on how I can extract that weekly profit vs Last Year and place it into my KPI to show Week on Week performance vs Last Year.

(I have tried all the obvious, but I feel I am just not seeing something!)


Thank you community!

[TomBurke]

Just to update another other users looking for Weekly Dax Time Intelligence, I found out what I needed.

Best to watch this video by Curbal on Dax Fridays.
https://www.youtube.com/watch?v=jBPLm5BHl2c

[AllisonKennedy]

SAMEPERIODLASTYEAR, PARALLELPERIOD and DATEADD can all do similar things but with slightly different levels of granularity/detail and behaviour when drilling down to week and month. Try DATEADD and PARALLELPERIOD and experiment with both in your table side by side to see the differences and which one you prefer. 

NOTE: PARALLELPERIOD will behave differently if you put in -12 MONTH than if you put in -1 YEAR. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[TomBurke]

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]

Hello @TomBurke ,

In essence, DATEADD expects a date table column as the first parameter like most Time Intelligence functions, which are designed to work with date/calendar tables (date dimensions). Time Intelligence,

For example,and ou have this table called MyDates

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

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

Return this:

As you can see column 4MonthsAfter for date 24/08/2016 value is blank since the column does not contain 24/12/2016 value, the same is true for 24/01/2017 because there is no value 24/05/2017. But 4 months after 24/09/2016 is calculated from 24/01/2017 is present in the column.

In addition, you can consult this article on the difference between Dateadd, ParallelPeriod and SamePeriodLastYear:

DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Weather Intelligence Question

Mejores looks,
Yingjie Li

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

[AllisonKennedy]

What do you mean by fictitious results? Are they higher or lower than you expect? Are you using the built in KPI visual or something else?

[TomBurke]

@AllisonKennedy ,

So in brief, figures for Week 18 and Week 19 are out.

Week 18 is -£40k Approx, and Week 19 is +£427K out from where they should be, however every other value matches up to what I am expecting. This is being viewed in a Matrix showing Week of Year and Total Profit by Years 2019 and 2020.

However, when converting the Matrix to a Month View (Month of Year) then the values I would expect to see are generated.

My frustration is coming from the exact sale DateAdd Field and Total Profit Measure, but visualising them into a week format seems to be causing the issue.

Sorry, this isn't as easy as first thought.
Its mainly frustrating as I can't even visualise this as a Matrix, as the values are incorrect. Any help would be appreciated!

[v-yingjl]

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]

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.