SUMX returning bad & crazy results

[Justin12000]

I'm trying to sum up a metric for a single day that is 7 days ago.

The correct answer, which functions fine on a chart, is 11635.6 on 1/27/2020

The answer I'm getting, is 651867 using this formula:

7DaysAgoHML = SUMX(  FILTER(AWS,FORMAT(AWS[Session Date].[Date],"MM-DD-YYYY") = FORMAT(NOW()-7,"MM-DD-YYYY")), AWS[HML])

I'm not sure what's going wrong here, it seems like a cartesian but it's a really simple query & it works elsewhere in the report

I've also tried something like:

Calculate( SUM( HML), FILTER( Date = Now()-7) )

 - That produced the same result

The only thing that marginally improved the result was hardcoding "01/27/2020" instead of NOW()-7 but that was only a slight improvement and no where near the desired result of 11635.6

The end game here, is I want a % of HML ~ 7 days ago / HML ~Today.  Is there a shortcut to do that?

Any and all help is appreciated!

[az38]

Hi @Justin12000 

first, you don't need to use format function if you are comparing two fields with the same Date type. second, there is no now() function in DAX. use today()

7DaysAgoHML = SUMX(  FILTER(AWS,AWS[Session Date] = (today()-7)), AWS[HML])

and you have completly wrong syntax in FILTER function in the second sentence. try like this

Calculate( SUM( HML), Date = (today()-7))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Have u checked now()-7 returning correct result?
If not try dateadd(table[date],-7,day)

Thanks
Pravin

If it resolves your problem mark it as a solution and give Kudos.

[amitchandak · Accepted solution]

Do you timestamp in your date, if yes create one more measure having only date part

date only = format(AWS[Session Date],"mm/dd/yyyy") and change the data type to date

if no timestamp then do not use .date use only AWS[Session Date]

Measure like this should work

Calculate( SUM( HML), FILTER(all(AWS[Session Date]),AWS[Session Date]= Now()-7) )

But prefer to create a date calendar and join the date with that and try

Calculate( SUM( HML), FILTER(all(Date),Date[Date] = Now()-7) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Justin12000]

This one worked! the Filter(all) combined with the Today() function

Thanks!