Comparing MTD this month with previous month on clustered column

[JM_nxgn]

Hello,

I'm trying to do comparisons of Profit between the current months Month to Day, and the previous Month's Month to day. 

So for example if it's the 23rd of September today. It compares the profits of the 1st - 23rd September to that of August 1st - 23rd.

So I can get this to work perfectly as a card and represent each month as needed, but I'm trying to include these in to a visual so lets say I want this current month and the two previous months, how do I go about doing this?

I tried to just add the two measures as 'values' in a clustered column chart but it doesn't work because it creates two columns side by side with a legend. I'm looking for two separate columns with appropriate axis, probably labelled by month.

The two measures I'm using are:

For Month to Yesterday: 

MTD_Earning = CALCULATE(SUM('Main Fact'[Earning]),

FILTER(DATESMTD(Dim_Date[Date]), Dim_Date[Date] < TODAY()))

For Previous Month to Same day as above:

Prev_MTD_Earning = CALCULATE(SUM('Main Fact'[Earning]),

DATEADD(FILTER(DATESMTD(Dim_Date[Date]), Dim_Date[Date] < TODAY()),-1, MONTH))

Thanks 🙂

[ibarrau]

Hi, let me write this down. Assuming you have a Dimension date with one day for each row without missing values (because it won't work if you don't have it) I think the measures should be like this

MTD = CALCULATE( SUM ( 'Main Fact'[Earning] ) ; DATESMTD(Dim_Date[Date]) ; Dim_Date[Date] < TODAY() )

Last 2 Months MTD = CALCULATE( [MTD] ; DATEADD( Dim_Date[Date] ; -2 ; MONTH ) )

Hope this helps,

Regards,

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

[JM_nxgn]

@ibarrau 

Hi, 

Thanks for your response.

So my issue isn't that my measures aren't working, they working just fine. As cards they look perfectly fine, but I'm trying to use these measures in a visual of sorts as below:

So firstly the two columns are sitting side by side without gap (i know I can create a measure with value 0 to 'create' a gap).

Secondly, there's no label for each column, possibly something like; "Oct MTD" and "Sep MTD".

[ibarrau]

I'm sorry I don't see the problem, can you ask the question of your needs?. Just add those measures to a bar chart as values and a date value or months as X axis and you will have the calculation for each month instead of having just one card.

[JM_nxgn]

@ibarrau 

So the reason I can't do that with the two measures is because I get this result:

So the two bars represent each of the measures. One of them does a Month to day for the current month and the other does a month to day for the previous month. When I add in months I get something like this which is not what I want at all. October should have Octobers MTD only, september should have Septembers MTD only, and August its own MTD. But all MTD can be comparitively compared, for example we are currently in the 23rd of October. So September should show 1st-22nd of September, August should show 1st-22nd of August and similarly for October. 

August only has one column because my dataset currently doesn't go any further back.

So I would need to create one measure which can do a Month to Day calculation for each month.

Can't use something like this:

MTD_Earning = CALCULATE(SUM('Main Fact'[Earning]),
FILTER(DATESMTD(Dim_Date[Date]), Dim_Date[Date] < TODAY()))

Because it will work for the latest month (October), but all the other months will calculate full months and not MTD's. 

[ibarrau · Accepted solution]

This is a strange request because the months don't have the same amount of days. You can try filtering like this:

DAY(Dim_Date[Date]) < DAY(TODAY()) )

The MTD of that day is the cumulative of the month until that day. The problem will be with months with 31 days because there is no 31 in all months. 

Hope this helps

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

[JM_nxgn]

Yeah I understand that. But it's just a way to create a visual which compares current profits with previous months. 

It wouldn't make sense for me to compare 23 days in October to a full previous month. I know issues kind of come in if we comparing a month of 31 days vs one with only 30, but it's probably the lesser of the two evils.