topic Is is necessary for Date table to Mark as date for calculation ? in DAX Commands and Tips

Is is necessary for Date table to Mark as date for calculation ?

KenMann — Fri, 01 Dec 2023 19:07:07 GMT

Hi guys,

May I know, is it necessary to mark a date table as Date, before using it for some calculation, for example to use it in SAMEPERIODLASTYER or PREVIOUSYEAR or DATEADD(date, -1, year) ?

Since I tried all those 3 DAX with my measure but all return wrongly, mostly blank.

I have FactTable named FactInternetSales and it has column SalesAmount. The measure I created are:

Sales amount = SUM(FactInternetSales[SalesAmount])

Sales amount LY = CALCULATE([Sales amount], SAMEPERIODLASTYEAR(Dates[Date])) or

Sales amount LY = CALCULATE([Sales amount], PREVIOUSYEAR(Dates[Date])) or

Sales amount LY = CALCULATE([Sales amount], DATEADD(Dates[Date], -1, year))

While the relationship is set between FactInternetSales[OrderDateKey] to Dates[DateKey]

Is it not working because I didn't mark the Dates table as Date ?

Thanks,

Re: Is is necessary for Date table to Mark as date for calculation ?

Greg_Deckler — Fri, 01 Dec 2023 21:21:59 GMT

KenMann Maybe, but those functions are terrible. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Also:

Re: Is is necessary for Date table to Mark as date for calculation ?

Anonymous — Wed, 06 Dec 2023 09:36:37 GMT

Hi KenMann ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Is is necessary for Date table to Mark as date for calculation ?

KenMann — Mon, 11 Dec 2023 15:31:28 GMT

Hi Xiaoxin,

I would love to, but may I know how to change the storage mode to "Import" ?

I guess this is the way if we want to share the pbix, right ?

Currently it is on Mixed mode :

I total won't mind about the data since it is on AdventureWorks and I'm on learning basis in Fabric.

A further explanation, the data set is from OneLakeHub, and all the tables is on Direct Query Mode ->

Btw, I currently did change my Date dimension to "Mark as date table", but the DAX calculation is still not working. As in previous message, I'm creating my 2 measures like this :

1. Sales amount = SUM(FactInternetSales[SalesAmount])

2. Sales amount LY = CALCULATE([Sales amount], SAMEPERIODLASTYEAR(Dates[Date])) or

Sales amount LY = CALCULATE([Sales amount], PREVIOUSYEAR(Dates[Date])) or

Sales amount LY = CALCULATE([Sales amount], DATEADD(Dates[Date], -1, year))

Thanks