Getting values from a relative period of the current row not working with datetime

[RogerSteinberg]

Hi,

I have this following table: 

Period	Start	End	Revenue	Revenue_1_week_ago	Min_Date_1_week_ago	Max_Date_1_week_ago
A	1/17/2021 14:00	1/24/2021 14:00	$1,000		1/10/2021 14:00	1/17/2021 14:00
B	2/11/2021 14:00	2/17/2021 4:59	$2,000		2/4/2021 14:00	2/10/2021 4:00

My formula for Min_Date_1_week_ago is :

Note that my transaction_date column is in datetime format

VAR min_date = min(AllPackages[transaction_date])

RETURN

min_date - (168/24)

As you can see it works fine because i want the same period from the week prior

My revenue formula is 

CALCULATE(

SUM(AllPackages[revenue]),

FILTER(

AllPackages,

AllPackages[transaction_date] >= [Min_Date_1_week_ago] && AllPackages[transaction_date]<=[Max_Date_1_week_ago]

)

)

I've tried adding the ALL inside the Filter function but doesnt work.

Im not sure why my revenue shows as blank

[v-yangliu-msft]

Hi  @RogerSteinberg  ,

I put your data into it and found these two problems

1. The data used in Sum cannot be of String type, you can check whether the type in [Revenue] is Text, if it is to be modified

2. Adjust your date types to be consistent, otherwise there will be no data

After I changed the date types to be consistent, the data is:

The result is:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RogerSteinberg]

THe max and min dates columns should not be columns but variables within measures. Since my report is really big, I would rather not have additional data points.

[AllisonKennedy]

@RogerSteinberg Are you doing these as calculated columns or measures? You need to consider the context the DAX calculates within. I can't tell what you're doing because your sample table has no Transaction_date column. Please share sample data set. 

[RogerSteinberg]

uhm really clueless how this report relates to my question.

my transaction_date column is in my model. its in a timestamp format. every sales figure are recorded on an hourly basis...