previous month and date table relation

[Ranahazim]

hi

i am having trouble geting correct data using both previous month formulas 

i did as instructed, created a date table, then i linked the date coulumn of it with a date coulumn in another table

i faced several problems: 

1- tried to create a calnder coulumn in my Account card table. it didnt work, then i linked the date table with one of the coulmns that are a date format. 

2- when performing the previous month function, it gives me the correct answer of every current month! not the previous month 

i used these: 

Previous_Month_current_balance = CALCULATE(SUM('EVS Account'[Current_Balance]),PREVIOUSMONTH('DimDate'[Date]))

and :

Previous_Month_current_balance = CALCULATE(SUM('EVS Account'[Current_Balance]),DATEADD(DimDate[Date],-1,MONTH))

i think my problem is with the relationship, what kind of date colomn should i link the date to?

please help. so frustrated

thanks

[v-yuta-msft · Accepted solution]

Hi Ranahazim,

What's relationship between 'DimDate' and 'EVS Account', many-to-one or one-to-many? In addtion, if possible, you can merge the tables and check if your measure can work.

Regards,

Jimmy Tao

[Ranahazim]

hi, 

the relationship is many to one 

I tried merging tables but after edit queries, the Dimdate table which i added wasnt visible to be merged. i added the dimdate table but not using sql query.

[Ranahazim]

am trying many things with no results yet