Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Chong SM]

Hi,

I am trying to compare sales use the above 3 time intelligent functions.
I believe there is nothing wrong with the DAX measures but I just couldn’t get all the past year sales to appear on the table.  E.g. 2017 sales should appear on 2018 across the 3 functions, and so on. File is attached for checking.

Any advice is appreciated.

Thanks!

——————————
Chong SM
——————————

[Vincent Lacomme]

Hi Chong,

You need a separate date table (you can check this excellent article on SQLBI : https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/).

Enclosed is the file where I only added a date table. I didn’t have to change your measures and now the visuals are working.

——————————
Vincent L.
Chartered accountant – Expert-comptable
——————————
——————————————-

[Chong SM]

Hi Vincent,

Thanks for the pointer. I have one more question regarding the dashboard (see my file attached).
Let’s say I want to make the ParallelPeriod, SamePeriodLastYear and DateAdd measures responding to the Region filter. As you can see I got an error message (see below) when the SamePeriodLastYear and DateAdd measures are added:

Even the ParallelPeriod measure is also not giving the correct reading after filtering.

Not sure if I have missed out anything, or there are some limitations in the measures, or I need to re-write the measures in a different way.

Thanks!

——————————
Chong SM
——————————
——————————————-

[Lutz Bendlin]

As it says in the error message your dates table need to have contiguous date entries (without any gaps). Make sure to point your DAX calculations to the right table, observe filter direction in your data model (avoid bi-directional where possible)  and make your dates table larger than your fact data by at least a year on either side.  Both the source and the destination of a date computation must be covered by the dates table range.

——————————
Lutz
——————————
——————————————-

[Chong SM]

Thanks for sharing.

I also found out I need to add .[Date] to DateAdd and SamePeriodLastYear measures. E.g.
 

DateAdd = CALCULATE([TotalSales], DATEADD(Sales[Date].[Date], -1, YEAR))

SamePeriodLastYear = CALCULATE([TotalSales], SAMEPERIODLASTYEAR(Sales[Date].[Date]))

Without the .[Date], the measures will give the above error message.

——————————
Chong SM
Data Analyst
——————————
——————————————-