Month on Month figures only showing when there are 31 days in a month

[dosania]

Hi,

I'm trying to calculate month on month change in some of my finance figures, and am writing a calcaultion in DAX to do this.

My SQL gives me the balance on the last day of the month for each month.

The DAX I have written to give me the Balance for Last month is below;

Balance LM = calculate(sum('Balance'[Amount]),dateadd('Balance'[Timekey],-1,month)

This formula only gives me the balance for the previous month, only if there are 31 days in the month. 

Below is an example of what is happening.

When I re-write the formula with the [date] reference as below, it gives me the same balance for this month and last month;

Balance LM = calculate(sum('Balance'[Amount]),dateadd('Balance'[Timekey].[Date],-1,month)

Any help on how to solve this would be much appreciated!

Thanks,

Anish

[v-janeyg-msft]

Hi, @dosania 

It’s my pleasure to answer for you.

According to your description, If you want to use the ‘dateadd’ function, then your date column must be a continuous date. But it seems that your date column has only one day per month, so you don’t need to use time intelligence function.

You can try like this:

Amount Last Month = 
SUMX(FILTER(ALL('Table'),[Date]=EOMONTH(SELECTEDVALUE('Table'[Date]),-1)),[Amount])

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[parry2k]

@dosania you should mark your date table as a date table, it is in my blog post on how to do it, and don't use DateTable[Date].[Date], it should be DateTable[Date]

[parry2k · Accepted solution]

@dosania as a best practice you should add date dimension in your model and perform time intelligence calculation based on that table instead of your transactional table. To add a simple date dimension, check one of my blog post here, and in your measure change balance[Date] to DateDimension[Date].

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dosania]

Thanks @parry2k. I have a date dimension in my table and have tried using this in the dax.

I've tried using the below;

Balance LM = calculate(sum('Balance'[Amount]),dateadd('DateTable'[Date],-1,month)

This gives me the following error message, 'MdxScript(Model) (97,76) Calculation error in measure 'Calculations'[Balance LM]: Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.'

When I try, Balance LM = calculate(sum('Balance'[Amount]),dateadd('DateTable'[Date].[date],-1,month),

it gives me the same balance for this month and last month.

Any thoughts would be great