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

[KenMann]

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,

[v-shex-msft]

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

[KenMann]

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

[Greg_Deckler]

@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-TIT...

Also: