Date Column Query

[ArchStanton]

Hi,

My new data model is calculating very high Year To Date numbers that never happened in the old data model. Could this code be aggregating rows possibly?

YTD New - Area = CALCULATE(TOTALYTD(COUNT('Cases'[Into Area Date]),'Date'[Date],"31/03"))

Also, the Measures are almost the same but I've noticed a difference in the Dates icon in the Fields table which I'm guessing maybe the cause?

Fig 1. This is the correct Data Model - the dates column is a Box split into 4:

New Data Model - why is this different? Could this be causing errors in my YTD Calculations?

Any help would be much appreciated

Thanks

[ArchStanton]

Thanks but Date is already formatted as date.

I haven't marked this as a date table because our developers said some problems can arise when doing so (the old data model also doesn't have the date table marked as a date table but it works just fine).

Could it be that the formula below  is causing the problem? In the old data model the date column was created in Power Query instead of DAX so it had no formula. Why are our numbers so high?

Date = CALENDAR(MINX('Cases','Cases'[tpo_legacycasecreationdate]),TODAY()+[FY Days Remaining])

Thanks,

I needed to populate the rest of this Financial Year upto 31st Mar 2023.

[amitchandak · Accepted solution]

@ArchStanton , Please mark the date table as date table. Right-click on the table and there is an option. Why date is not shown as date or as key? Check Data type of date

Refer

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...