Getting issues in Time Intelligence calculations (YTD, QTD, MTD) - pbix and dax query attached.

[ajit_singh]

Getting issue when the below dax query is run in dax studio:

1. When time_date[time_date_id] is included, none of ytd, qtd or mtd is calculated.

2. When time_date[time_date_id] is excluded, only mtd works, ytd or qtd is calcuated same as mtd.

pbix and dax query is as below:

pbix file location: 

https://github.com/business-data-analytics/powerbi/raw/main/assets/ajit-dax-modeling.pbix

DAX query:

DEFINE
MEASURE f2[m1_sum] = sum(f2[m1])
MEASURE f2[m1_count] = countx(f2, f2[m1])
MEASURE f2[m1_ytd] = CALCULATE ( f2[m1_sum], DATESYTD ( time_date[time_date] ) ) 
MEASURE f2[m1_ytd1] = TOTALYTD([m1_sum], time_date[time_date])
MEASURE f2[m1_ytd2]  = CALCULATE ( [m1_sum], VAR LastDateAvailable = MAX ( time_date[time_date] ) VAR FirstJanuaryOfLastDate = DATE ( YEAR ( LastDateAvailable ), 1, 1 ) RETURN FILTER ( ALL ( time_date[time_date] ), AND ( time_date[time_date] >= FirstJanuaryOfLastDate, time_date[time_date] <= LastDateAvailable ) ) )
MEASURE f2[m1_qtd]  = CALCULATE([m1_sum],DATESQTD(time_date[time_date]))
MEASURE f2[m1_mtd]  = CALCULATE([m1_sum],DATESMTD(time_date[time_date]))

EVALUATE
SUMMARIZECOLUMNS(
  time_date[time_cy_id]
, time_date[time_cqtr_id]
, time_date[time_cmon_id]
--, time_date[time_date_id]
, time_date[time_date]
, "m1_sum", f2[m1_sum]
, "m1_count", f2[m1_count]
, "m1_ytd" , f2[m1_ytd]
, "m1_ytd1" , f2[m1_ytd1]
, "m1_ytd2" , f2[m1_ytd2]
, "m1_qtd" , f2[m1_qtd]
, "m1_mtd" , f2[m1_mtd]
)
order by 
 time_date[time_date]

Thanks,

Ajit Singh.

[ajit_singh]

@Fowmy  thank you so much for the correct solution and quick response.

[Fowmy · Accepted solution]

@ajit_singh 

For the time intelligence functions to work as expected, you need to mark your data table as a Date Table in the model.

Click on Data View select your time_date table, click on "Mark as Date Table"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn