Forum Discussion
summing a column value between two specific dates
I am trying to sum a sales column between two dates as follows :
Measure = CALCULATE(Sum'table name'[col. name], DATESBETWEEN('date table'[date], DATE(2022,03,01),DATE(2022,03,15))It is returning nothing.
The date table is generated thru' Calendarauto() function and is related to the main table date column called "purchase date". The main column has other date columns though.
I have also tried writing the measure with the date column within the main table - it still does not work.
Am I missing something or the relationship between the date table and the query table is not set up right?
Any help appreciated.
Thanks
monojchakrab if you need the timestamp in your analysis, you can simply duplicate the timestamp column and then change the column data type to Date and use it when you need only date.
monojchakrab , your measure was correct.
The reason it diden't work is becasue the relationship between the auto date table (or any standatd date table you would have made) is based on the date column. This is not a group by relationship -> 01/01/2022 in the date table <> 01/01/2022 00:01 in the main table, so nothing was picked up in the relationship. If you want to use time intelligence calculations -> you need a date table -> you need a date column in the related table (main/fact). If you need to also do calulcaitons based on time, the best practice is to split the original column into date column and time column (by they way both of them are date/time columns in the tabular model but the date column corresponds to dates with no value in the time and the time column corresponds to to deifferent times in 30/12/1899). Than you can make a dimenstion table for the time colunm - alltough not necessary if you only have 1 fact table, but allways a good idea if you are going to use this column in filters (due to the auto exist behaviour in summarizecloumns function which is used pretty much for every pivotal visual in Power BI)SpartaBI I totally agree. monojchakrab The date tables are important in Power BI because you'll need them to reference dates inside your model. On top of that, building a date table can help if you need specific date information for your reports. It's important to get your date tables right if you plan on using them for time intelligence calculations.
9 Replies
monojchakrab , your measure was correct.
The reason it diden't work is becasue the relationship between the auto date table (or any standatd date table you would have made) is based on the date column. This is not a group by relationship -> 01/01/2022 in the date table <> 01/01/2022 00:01 in the main table, so nothing was picked up in the relationship. If you want to use time intelligence calculations -> you need a date table -> you need a date column in the related table (main/fact). If you need to also do calulcaitons based on time, the best practice is to split the original column into date column and time column (by they way both of them are date/time columns in the tabular model but the date column corresponds to dates with no value in the time and the time column corresponds to to deifferent times in 30/12/1899). Than you can make a dimenstion table for the time colunm - alltough not necessary if you only have 1 fact table, but allways a good idea if you are going to use this column in filters (due to the auto exist behaviour in summarizecloumns function which is used pretty much for every pivotal visual in Power BI)SpartaBI I totally agree. monojchakrab The date tables are important in Power BI because you'll need them to reference dates inside your model. On top of that, building a date table can help if you need specific date information for your reports. It's important to get your date tables right if you plan on using them for time intelligence calculations.
Can you please share your model ?
Also are you sure that you have data for the range date that you provided ?
Dax model queryDate tableRelationshipMeasure with dates from date tableMeasure with date from query table
What is the format for :
-Date in Date Table
-PurchaseDate in the Amazon YTD Apr22
The screen shots with the measure will also show that the dates chosen do have data in the query table - so the date column from the Date table should be able to query the same dates from the table and sum the column for sales which in this case is named as "item price"
Is it possible that the time stamp on the [purchase date] column in the query table is different from those on the [date] field in the date table?
Is it possible to keep the time stamps as those are important information
I removed the time stamp from the [purchase date] column in the main query table and now somehow it seems to work.
But not sure that's the right approach as I am losing the time stamp on the sales which I must have for other analysisseems to worktime stamp removed
monojchakrab if you need the timestamp in your analysis, you can simply duplicate the timestamp column and then change the column data type to Date and use it when you need only date.
